<?php
    define("UPLOAD_EXCEL_DIR", WWW_ROOT.'files'.DS.'batch_excel');
    define("DOWNLOAD_EXCEL_DIR", WWW_ROOT.'files'.DS.'batch_excel_download');

    App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel'.DS.'Classes'.DS.'PHPExcel.php'));
    App::import('Vendor', 'TCPDF', array('file' => 'TCPDF'.DS.'tcpdf.php'));
    //App::uses('UploadPDF','Vendor/FPDF');
    App::uses('CakeEmail', 'Network/Email');
    
/*
 * Payroll Controller class
 * PHP versions 5.1.4
 * @filesource
 * @author   Manisha/Reetu
 * @version 0.0.1 
 *   - Initial release 
 */


class PayrollsController extends AppController {
    public $uses = array('BatchList', 'Payable', 'Payee', 'User', 'PaymentType');
    public $components = array('Email');
    /* __________________________________________________________
     * @Method      :beforeFilter
     * @Description :to set up the Auth component
     * @access      :null
     * @param      :null
     * @return     :null
     */
    function beforeFilter() {
        ini_set('memory_limit', '512M');
        //--------- Directory Creation------------
        if (!is_dir(UPLOAD_EXCEL_DIR)) {
            mkdir(UPLOAD_EXCEL_DIR, 0777);         
        }
        if (!is_dir(DOWNLOAD_EXCEL_DIR)) {
            mkdir(DOWNLOAD_EXCEL_DIR, 0777);         
        }
        $this->layout = 'home';
        parent::beforeFilter();
    }
    
    /* __________________________________________________________
     * @Method      :Index
     * @Description :To show list of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function index() {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        
        $savecrit = '';

        //criteria array for search
        $crit = array();
        $crit[] = "";
        
        $filterMonth = $filterYear = '';
        $conditions = array();
        if ($this->request->data) {
            //pr($this->request->data);
            $filterDate = $filterDateMin = $filterDateMax = '';
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] != ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
                
                $filterMonth = $this->request->data['Payroll']['month'];
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] == ''){
                $filterDateMin = $this->request->data['Payroll']['year'].'-1-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-12-31';
                
                $filterMonth = 1;
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if ($this->request->data['Payroll']['search_criteria'] != '' && $filterDateMin == '' && $filterDateMax == '') {
                $searchCriteriaTerm = trim($this->data['Payroll']['search_criteria']);
                //will search with name and abbreviation
                //$crit[] = "(BatchList.unit_code like '%" . $searchCriteriaTerm . "%')";
                $conditions = array('BatchList.unit_code like' => "%" . $searchCriteriaTerm . "%");

                //$savecrit .= "search_criteria:" . $searchCriteriaTerm . "/";
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->data['Payroll']['search_criteria'] == '') {
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->request->data['Payroll']['search_criteria'] != '') {
                $searchCriteriaTerm = trim($this->request->data['Payroll']['search_criteria']);
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax,
                    'BatchList.unit_code LIKE ' => "%" . $searchCriteriaTerm . "%",
                );
            }
            //echo 'Date Min'. $filterDateMin;
            //echo '<br>Date Maxs'. $filterDateMax;
        } else{
            $filterDateMin = date("Y-m-1");
            $filterDateMax = date("Y-m-t");
            $conditions = array(
                'BatchList.prepared_date >= ' => $filterDateMin,
                'BatchList.prepared_date <= ' => $filterDateMax
            );
            
            $filterMonth = date("m");
            $filterYear = date("Y");
        }
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $conditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($conditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }

        /*$batchDetails = $batchData;
        $newBatachData = $tempArray = array();
        if(!empty($batchDetails)){
            foreach($batchDetails as $key => $details){
                foreach($details['BatchList'] as $subKey => $value){
                    $tempArray['BatchList'][$subKey] = $value;
                    if($subKey == 'unit_code'){
                        $tempArray['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
                    }
                }
                $newBatachData[$key] = $tempArray; 
                $newBatachData[$key]['BatchList']['payable_count'] = count($details['Payable']); 
                //$batchData[$key]['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
            }
        }*/
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :view
     * @Description :To view details of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function view($id  = null) {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');

        if (!$id) {
            throw new NotFoundException(__('INVALID_PAYROLL'));
        }

        $savecrit = '';
        //criteria array for search
        $crit = array();

        if (isset($this->request->data)) {

            if (isset($this->request->data['BatchList']['search_criteria']) && $this->request->data['BatchList']['search_criteria'] != '') {
                $searchCriteriaTerm = trim($this->request->data['BatchList']['search_criteria']);
                //will search with first name and email and last name
                $crit[] = "(Payable.e_name like '%" . $searchCriteriaTerm . "%'" . " OR Payable.c_name like '%" . $searchCriteriaTerm . "%')";
                $savecrit .= "search_criteria:" . $searchCriteriaTerm . "/";
            }

            if (isset($this->request->data['BatchList']['holdShow']) && $this->request->data['BatchList']['holdShow'] != '') {
                $holdShow = trim($this->request->data['BatchList']['holdShow']);

                if ($holdShow == 2) {
                    //will search with first name and email and last name
                    $crit[] = "(Payable.on_hold = 1)";
                    $savecrit .= "on_hold:" . $holdShow . "/";
                }
            }
        }
        //for passing the values of search conditions in params of paging to next page
        if (count($this->params['named']) > 0) {
            foreach ($this->params['named'] as $paramsKey => $paramsValue) {
                $savecrit .= $paramsKey . ":" . $paramsValue . "/";
            }

            if (isset($this->params['named']['search_criteria'])) {
                $crit[] = "(Payable.e_name like '%" . $this->params['named']['search_criteria'] . "%'" . " Payable.c_name like '%" . $this->params['named']['search_criteria'] . "%')";
            }

            if (isset($this->params['named']['on_hold'])) {
                if ($this->params['named']['on_hold'] == 2) {
                    $crit[] = "(Payable.on_hold = 1)";
                }
            }
        }//if

        $this->BatchList->recursive = -1;
        $batchListData = $this->BatchList->findById($id);

        $this->Payable->recursive = -1;
        $crit[] = "(Payable.batch_list_id  = $id)";


        if (count($crit) > 0) {
            $criteria = implode(" AND ", $crit);
        } else {
            $criteria = "";
        }


        $this->Payable->virtualFields = array('amount' => 'ROUND(Payable.amount_payable)');
        $payableList = $this->paginate('Payable', array($criteria));

        $usersData = array();
        if (!empty($batchListData)) {
            $usersIncluded = array();
            $usersIncluded[] = $batchListData['BatchList']['prepared_by'];
            $usersIncluded[] = $batchListData['BatchList']['verified_by'];
            $usersIncluded[] = $batchListData['BatchList']['approved_by'];
            $usersIncluded[] = $batchListData['BatchList']['endorse_by'];
            $usersIncluded[] = $batchListData['BatchList']['checker'];
            $usersIncluded[] = $batchListData['BatchList']['admin_verifier'];
            $usersIncluded = array_unique($usersIncluded);

            $fileDatas = $this->User->find('all', array(
                'conditions' => array('User.id' => $usersIncluded),
                'fields' => array('User.e_name', 'User.id')
            ));
            foreach ($fileDatas as $fetcheUserData) {
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }

        $this->set('payableList', $payableList);
        $this->set('usersData', $usersData);

        if (!$batchListData) {
            throw new NotFoundException(__('INVALID_BATCH'));
        } else {
            $this->set('batchListData', $batchListData);
        }

        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('payable_list', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :uploadBatch
     * @Description :To view details of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function uploadBatch() {
        $roleId = $this->Auth->User('role_id');;
        //Configure::write('debug', 0);
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        $this->autoRender = false;
        $this->layout = '';
        
        if ($this->request->is('post')) {
            $this->Payable->create();     

            $this->Payable->set($this->request->data['Payroll']);
            if ($this->Payable->validates()) {
                $tmp_name = $this->request->data['Payroll']["excel_file"]["tmp_name"];
                $name = $this->request->data['Payroll']["excel_file"]["name"];
                $newFileName = date('H_i').'_'.str_replace(" ", "_", $name);
                
                $dateFolder = date('M_d_Y');
                $uploadDir = UPLOAD_EXCEL_DIR.DS.$dateFolder;
                //--------- Directory Creation------------
                if (!is_dir($uploadDir)) {
                    mkdir($uploadDir, 0777);         
                }
                if(move_uploaded_file($tmp_name, $uploadDir.DS.$newFileName)){
                    $uploadedFile = $uploadDir.DS.$newFileName;
                    if (!is_readable($uploadedFile)) {
                        $this->error( 'File not found' );
                        return false;
                    }
                    
                    if(!$roleId){
                        App::Import("Model", "ServiceUnit");
                        $this->ServiceUnit = new ServiceUnit();
                        $userServiceUnitCode = $this->ServiceUnit->find('list', array('fields' => array('ServiceUnit.id', 'ServiceUnit.unit_code')));
                    } else {
                        App::Import("Model", "UserServiceUnit");
                        $this->UserServiceUnit = new UserServiceUnit();
                        $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                                    'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                                    'fields' => array('UserServiceUnit.service_unit_id'))
                                                );
                        App::Import("Model", "ServiceUnit");
                        $this->ServiceUnit = new ServiceUnit();
                        $userServiceUnitCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.id', 'ServiceUnit.unit_code')));
                        //pr($serviceCode);
                    }
                    
            
                    App::Import("Model", "PaymentType");
                    $this->PaymentType = new PaymentType();
                    $paymentTypes = $this->PaymentType->find('list', array('fields' => array('PaymentType.id')));
                    //pr($paymentTypes);
                    
                    App::Import("Model", "Payee");
                    $this->Payee = new Payee();
                    $payees = $this->Payee->find('list', array('fields' => array('Payee.id', 'Payee.payee_id_proof_no')));
                    
                    App::Import("Model", "Payable");
                    $this->Payable = new Payable();
                    $payables = $this->Payable->find('list', array('fields' => array('Payable.id', 'Payable.payee_id_proof_no')));
                    
                    /*App::Import("Model", "ServiceUnit");
                    $this->ServiceUnit = new ServiceUnit();
                    $serviceUnit = $this->ServiceUnit->find('list', array('fields' => array('ServiceUnit.id', 'ServiceUnit.unit_code')));*/
                    
                    /*App::Import("Model", "BatchList");
                    $this->BatchList = new BatchList();
                    $batchLists = $this->BatchList->find('list', array(
                                    'conditions' => array('BatchList.approved_status' => 0), 
                                    'fields' => array('BatchList.id', 'BatchList.batch_no_id')
                                ));*/
                   // pr($batchLists);
                    
                    //---------- Parsing Start--------------------
                    $inputFileType = PHPExcel_IOFactory::identify($uploadedFile);
                    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                    $objReader->setReadDataOnly(true);
                    $objPHPExcel = $objReader->load($uploadedFile);
                    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
                    $highestRow = $objWorksheet->getHighestRow();
                    $highestColumn = $objWorksheet->getHighestColumn();
                    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
                    
                    $paymentTypeIdArray = $payeeIdArray = $batchIdArray = $payeesData = $columnNameArray = array();
                    $paymentTypeIdColumn = $payeeIdColumn = $batchNoIdColumn = $errors = '';
                    
                    $firstColumn = trim($objWorksheet->getCellByColumnAndRow(0, 1)->getValue());
                    $secondColumn = $objWorksheet->getCellByColumnAndRow(1, 1)->getValue();

                    $serviceUnitError = false;
                    if($firstColumn == 'unit_code'){
                        $grandTotalIncome = 0;
                        for ($row = 2; $row <= $highestRow;  ++$row) {
                            for ($col = 0; $col < $highestColumnIndex;  ++$col) {
                                $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                                //$arraydata[$row - 1][$col] = $value;
                                /*if($value == 'unit_code' && $unitCodeColumn == ''){
                                    $unitCodeColumn = $col;
                                }*/
                                if($value == 'payment_type_id' && $paymentTypeIdColumn == ''){
                                    $paymentTypeIdColumn = $col;
                                }
                                /*if($value == 'payee_id_proof_no' && $payeeIdColumn == ''){
                                    $payeeIdColumn = $col;
                                }*/
                                if($row == 2){
                                    $columnNameArray[$value] = $col;
                                }
                            }
                            //$unitCodeVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['unit_code'], $row)->getValue();
                            $unitCodeVal = $secondColumn;
                            $paymentTypeIdVal = isset($columnNameArray['payment_type_id']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_type_id'], $row)->getCalculatedValue() : '';
                            $eNameVal = isset($columnNameArray['e_name']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getValue() : '';
                            $payeeIdProofVal = isset($columnNameArray['payee_id_proof_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getValue() : '';
                            $dateBeginVal = isset($columnNameArray['date_begin']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['date_begin'], $row)->getValue() : '';
                            $dateEndVal = isset($columnNameArray['date_end']) ?  $objWorksheet->getCellByColumnAndRow($columnNameArray['date_end'], $row)->getValue() : '';
                            $amtPayableVal = isset($columnNameArray['amt_payable']) ?  $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getValue() : '';
                            //$bankAccountNoVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['bank_account_no'], $row)->getValue();
                            $MPFVal = isset($columnNameArray['MPF']) ?  $objWorksheet->getCellByColumnAndRow($columnNameArray['MPF'], $row)->getCalculatedValue() : '';

                            if(($unitCodeVal != '') && ($paymentTypeIdVal != '') && ($eNameVal != '') && ($payeeIdProofVal != '') && ($dateBeginVal != '') && ($dateEndVal != '') && ($MPFVal != '')){
                                //if($row == 2){
                                    //$unit_code = $objWorksheet->getCellByColumnAndRow($columnNameArray['unit_code'], $row)->getValue();
                                    $batchData['BatchList'] = array(
                                        //'batch_no_id' => $objWorksheet->getCellByColumnAndRow($columnNameArray['batch_no_id'], $row)->getValue(),
                                        'batch_no_id' => '',
                                        'unit_code' => $unitCodeVal,
                                        'prepared_by' => $userId,
                                        'prepared_date' => date('Y-m-d'),
                                        'approved_status' => 0
                                    );
                                    if(!in_array($unitCodeVal, $userServiceUnitCode) && !$serviceUnitError){
                                        $errors .= __('UNIT_CODE'). ' ' . $unitCodeVal . ' ' .__('IS_NOT_ALLOCATED_TO_YOU').' OR '.__('IS_NOT_AVAILABLE').'<br>';
                                        $serviceUnitError = true;
                                    }
                                //}

                                $payeeIdProofNo = $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getValue();
                                if(!in_array($payeeIdProofNo, $payees)){
                                    $payeesUserData[]['Payee'] = array(
                                        'payee_id_proof_no' => $payeeIdProofNo,
                                        'eng_name' => $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getValue(),
                                        'chi_name' => $objWorksheet->getCellByColumnAndRow($columnNameArray['c_name'], $row)->getValue(),
                                        'email' => '',
                                        'phone' => '',
                                        'recorded_by' => $userId,
                                        'last_created_by' => $userId
                                    );
                                    $payees[] = $payeeIdProofNo;
                                }

                                $phpDateValueBegin = $phpDateValueEnd = time();
                                if(isset($columnNameArray['date_begin'])){
                                    $excelDateValueBegin = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_begin'], $row)->getValue();
                                    $phpDateValueBegin = PHPExcel_Shared_Date::ExcelToPHP($excelDateValueBegin);
                                }

                                if(isset($columnNameArray['date_end'])){
                                    $excelDateValueEnd = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_end'], $row)->getValue();
                                    $phpDateValueEnd = PHPExcel_Shared_Date::ExcelToPHP($excelDateValueEnd);
                                }

                                $payeesData[]['Payable'] = array(
                                    //'payee_id' => $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id'], $row)->getValue(),
                                    'payment_type_id' => isset($columnNameArray['payment_type_id']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_type_id'], $row)->getCalculatedValue() : '',
                                    'c_name' => isset($columnNameArray['c_name']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['c_name'], $row)->getCalculatedValue() : '',
                                    'e_name' => isset($columnNameArray['e_name']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getCalculatedValue() : '',
                                    'payee_id_proof_no' => isset($columnNameArray['payee_id_proof_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getCalculatedValue() : '',
                                    'payment_subject' => isset($columnNameArray['payment_subject']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_subject'], $row)->getCalculatedValue() : '',
                                    'payment_begin' =>  date('Y-m-d', $phpDateValueBegin),
                                    'payment_end' => date('Y-m-d', $phpDateValueEnd),
                                    'course_head_count' => isset($columnNameArray['head_cnt']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['head_cnt'], $row)->getCalculatedValue() : '',
                                    'course_fee' => isset($columnNameArray['course_fee']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['course_fee'], $row)->getCalculatedValue() : '',
                                    'total_fee_income' => isset($columnNameArray['total_income']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['total_income'], $row)->getCalculatedValue() : '',
                                    'income_splite_remarks' => isset($columnNameArray['remark_share']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['remark_share'], $row)->getCalculatedValue() : '',
                                    'course_remarks' => isset($columnNameArray['remark_gen']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['remark_gen'], $row)->getCalculatedValue() : '',
                                    'amount_payable' => isset($columnNameArray['amt_payable']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getCalculatedValue() : '',
                                    //'bank_account_no' => isset($columnNameArray['bank_acct_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['bank_acct_no'], $row)->getCalculatedValue() : '',
                                    'mpf_entitlement' => isset($columnNameArray['MPF']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['MPF'], $row)->getCalculatedValue() : '',
                                    //'chk418' => isset($columnNameArray['chk418']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['chk418'], $row)->getCalculatedValue() : '',
                                    //'chkreserve' => isset($columnNameArray['chkreserve']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['chkreserve'], $row)->getCalculatedValue() : '',
                                    'contract_sheet_no' => isset($columnNameArray['contract_sheet_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['contract_sheet_no'], $row)->getCalculatedValue() : '',
                                );
                                $payables[] = $payeeIdProofNo;
                                $grandTotalIncome += isset($columnNameArray['amt_payable']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getCalculatedValue() : 0 ;
                            } else {
                                if($unitCodeVal == ''){$errors .= 'unit_code ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($paymentTypeIdVal == ''){$errors .= 'payment_type_id ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($eNameVal == ''){$errors .= 'e_name ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($payeeIdProofVal == ''){$errors .= 'payee_id_proof_no ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($dateBeginVal == ''){$errors .= 'date_begin ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($dateEndVal == ''){$errors .= 'date_end ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($amtPayableVal == ''){$errors .= 'amt_payable ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                //if($bankAccountNoVal == ''){$errors .= 'bank_acct_no ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                if($MPFVal == ''){$errors .= 'MPF ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                            }
                        }
                    } else{
                        $errors .= __('FIRST_COLUMN_NAME_ERROR').' '.__('UNIT_CODE'). ' should be there.';
                    }

                    if(empty($errors)){
                        if(!empty($payeesData)){
                            $this->saveActionLog('batch', 'Batch Upload for Unit Code :'. $batchData['BatchList']['unit_code']);
                            /*if(!empty($batchIdArrayToDelete)){
                                $condition = array('BatchList.id' => $batchIdArrayToDelete);
                                $this->BatchList->deleteAll($condition, FALSE);
                            }*/
                            $this->Payee->validator()->remove('eng_name');
                            $this->Payee->validator()->remove('chi_name');
                            $this->Payee->validator()->remove('phone');
                            $this->Payee->validator()->remove('email');
                            $this->Payee->validator()->remove('bank_account_no');
                            if(!empty($payeesUserData)){
                                $this->Payee->saveMany($payeesUserData);
                            }
                            $payees = $this->Payee->find('list', array('fields' => array('Payee.id', 'Payee.payee_id_proof_no')));

                            $batchData['BatchList']['total_amount'] = $grandTotalIncome;
                            $this->BatchList->save($batchData);
                            $batchId = $this->BatchList->getLastInsertID();

                            //----------Payables Data-----------
                            $payeesDataNew = array();

                            foreach($payeesData as $retrivedData){
                                $retrivedData['Payable']['batch_list_id'] = $batchId;
                                //$retrivedData['Payable']['payee_id_proof_no'] = array_search($retrivedData['Payable']['payee_id_proof_no'], $payees);
                                $payeesDataNew[] = $retrivedData;
                            }
                            unset($payeesDataNew[0]);

                            $this->Payable->saveMany($payeesDataNew);
                            $msg = __("FILE_UPLOAD_SUCCESS", true);
                            $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
                        } else{
                            $msg = __("NO_PAYABLE_IN_BATCH", true);
                            $this->Session->setFlash('<div class="totalErrors">' . $msg . '</div>');
                        }
                        return $this->redirect(array('action' => 'index'));
                    } else{
                        $this->Session->setFlash('<div class="totalErrors">' . $errors . '</div>');
                        return $this->redirect(array('action' => 'index'));
                    }
                }
            } else {
                $msg = __("INVALID_FILE_TYPE", true). ' ' .__("ONLY_XLS_ALLOWED", true);
                $this->Session->setFlash('<div class="totalErrors">' . $msg . '</div>');
                return $this->redirect(array('action' => 'index'));
            }
        }
    }
    
    /* __________________________________________________________
     * @Method      :download
     * @Description :to download listing of Payroll Batch List in Excel/PDF
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function download() {
        $this->autoRender = false;
        $this->layout = '';
        if($this->request->data){
            $userId   = $this->Auth->User("id");
            $userType = $this->Auth->User('user_type');
            
            $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
            $maxDate = date("t", strtotime($filterDate));
            $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
            $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
            
            $conditions = array(
                'BatchList.prepared_date >= ' => $filterDateMin,
                'BatchList.prepared_date <= ' => $filterDateMax
            );
            
            App::Import("Model", "ServiceUnit");
            $this->ServiceUnit = new ServiceUnit();

            $serviceUnitCondition = array();
            //=============Service Unit Associated to logged in user==================
            if($userId !=1 ){
                App::Import("Model", "UserServiceUnit");
                $this->UserServiceUnit = new UserServiceUnit();
                $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                            'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                            'fields' => array('UserServiceUnit.service_unit_id'))
                                        );
                $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
                //pr($serviceCode);
                $conditions['BatchList.unit_code'] = $serviceCode;
                $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
            }
            
            //=============Service Unit Name for Associated Unit==================
            $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));

            
            //================= Batch data in selected Date period======================
            $this->BatchList->recursive = -1;
            $batchData = $this->BatchList->find('all', array('conditions' => $conditions));
            
            //==========User Data for User Included in this batch List==============
            
            $userData = array();
            if(!empty($batchData)){
                $usersIncluded = $unitIncluded = array();
                foreach ($batchData as $fetcheddData){
                   $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                   $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                   $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                   $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                   $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                   $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
                   $unitIncluded[] = $fetcheddData['BatchList']['unit_code'];
                }
                $usersIncluded = array_unique($usersIncluded);
                $fileDatas = $this->User->find('all', array(
                            'conditions' => array('User.id' => $usersIncluded),
                            'fields' => array('User.e_name', 'User.id')
                        ));
                foreach($fileDatas as $fetcheUserData){
                    $userData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
                }
            }
        
            //-------Details of User who is downloading ------------
            $this->User->recursive = -1;
            $userDetails = $this->User->findById($userId);
            
            //------------Excel File creation starts here--------------------
            if(isset($this->request->data['print_excel'])){
                $objPHPExcel = new PHPExcel(); // Excel Object
                // Set properties
                $objPHPExcel->getProperties()->setCreator($userDetails['User']['e_name']);
                $objPHPExcel->getProperties()->setLastModifiedBy($userDetails['User']['e_name']);
                $objPHPExcel->getProperties()->setTitle("Batch List");
                $objPHPExcel->getProperties()->setSubject("Batch List");
                $objPHPExcel->getProperties()->setDescription("Batch List document.");


                // Add some data
                $objPHPExcel->setActiveSheetIndex(0);
                if(!empty($batchData)){
                    $i = 1;
                    foreach($batchData as $key => $value){
                        unset($value['BatchList']['id']);
                        unset($value['BatchList']['batch_no_id']);
                        unset($value['BatchList']['created']);
                        unset($value['BatchList']['modified']);
                        if($i == 1){
                            $chr = 65;
                            $objPHPExcel->getActiveSheet()->getColumnDimension(chr($chr))->setAutoSize(true);
                            $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, 'unit_name');
                            $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).($i+1), $serviceUnit[$value['BatchList']['unit_code']]);
                            $chr = 66;
                            foreach ($value['BatchList'] as $header => $cellValue){
                                $objPHPExcel->getActiveSheet()->getColumnDimension(chr($chr))->setAutoSize(true);
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, $header);
                                if (in_array($header, array('prepared_by', 'approved_by', 'checker', 'admin_verifier', 'verified_by', 'endorse_by', 'admin_verifier'))) {
                                    $cellValue = isset($userData[$cellValue]) ? $userData[$cellValue] : '';
                                }
                                if($header == 'approved_status'){
                                    $cellValue = ($cellValue) ? 'Approved' : 'Yet to Approve';
                                }
                                if($header == 'total_amount'){
                                    $cellValue = number_format($cellValue, 2);
                                }
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).($i+1), $cellValue);
                                $chr++;
                            }
                            $i++;
                        } else{
                            $chr = 65;
                            $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, $serviceUnit[$value['BatchList']['unit_code']]);
                            $chr = 66;
                            foreach ($value['BatchList'] as $header => $cellValue){
                                if (in_array($header, array('prepared_by', 'approved_by', 'checker', 'admin_verifier', 'verified_by', 'endorse_by', 'admin_verifier'))) {
                                    $cellValue = isset($userData[$cellValue]) ? $userData[$cellValue] : '';
                                }
                                if($header == 'approved_status'){
                                    $cellValue = ($cellValue) ? 'Approved' : 'Yet to Approve';
                                }
                                if($header == 'total_amount'){
                                    $cellValue = $cellValue;
                                }
                                $objPHPExcel->getActiveSheet()->getColumnDimension(chr($chr))->setAutoSize(true);
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, $cellValue);
                                $chr++;
                            }
                        }
                        $i++;
                    }
                }

                $objPHPExcel->getActiveSheet()->setTitle($filterDateMin. ' to '.$filterDateMax);
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="BatchList.xlsx"');
                header('Cache-Control: max-age=0');

                $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
                $objWriter->save('php://output');
                exit();
            }
            if(isset($this->request->data['print_pdf'])){

                Configure::write('debug', 0);
                //echo 'PDF Vork';
                $pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
                
                // set document information
                $pdf->SetCreator(PDF_CREATOR);
                $pdf->SetAuthor($userDetails['User']['e_name']);
                $pdf->SetTitle('Batch List');
                //$pdf->SetSubject('Batch List');

                // set default header data
                //$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,64,255), array(0,64,128));
                //$pdf->setFooterData(array(0,64,0), array(0,64,128));

                // set header and footer fonts
                //$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
                //$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

                // set default monospaced font
                $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

                // set margins
                $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
                //$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
                //$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

                // set auto page breaks
                $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

                // set image scale factor
                $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

                // set some language-dependent strings (optional)
                if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
                    require_once(dirname(__FILE__).'/lang/eng.php');
                    $pdf->setLanguageArray($l);
                }

                // ---------------------------------------------------------

                // set default font subsetting mode
                $pdf->setFontSubsetting(true);

                // Set font
                // dejavusans is a UTF-8 Unicode font, if you only need to
                // print standard ASCII chars, you can use core fonts like
                // helvetica or times to reduce file size.
                $pdf->SetFont('kozminproregular', '', 10, '', true);

                // Add a page
                // This method has several options, check the source code documentation for more information.
                $pdf->AddPage();
                if (!empty($batchData)) {
                    $i = 0;
                    $html = '<h2>Batch List: From '.$filterDateMin. ' to '.$filterDateMax.'</h2>
                            <table border="1" cellspacing="0" cellpadding="6">';
                            foreach($batchData as $batchDetails){
                                unset($batchDetails['BatchList']['id']);
                                unset($batchDetails['BatchList']['batch_no_id']);
                                unset($batchDetails['BatchList']['created']);
                                unset($batchDetails['BatchList']['modified']);
                                unset($batchDetails['BatchList']['batch_no_id']);
                                if($i == 0){
                                    $html .= '<tr>';
                                    $html .= '<th>unit_name</th>';
                                    foreach ($batchDetails['BatchList'] as $header => $cellValue) {
                                        $html .= '<th>'.$header.'</th>';
                                    }
                                    $html .= '</tr><tr>';
                                    $html .= '<td>'.$serviceUnit[$batchDetails['BatchList']['unit_code']].'</td>';
                                    foreach ($batchDetails['BatchList'] as $header => $cellValue) {
                                        if (in_array($header, array('prepared_by', 'approved_by', 'checker', 'admin_verifier', 'verified_by', 'endorse_by', 'admin_verifier'))) {
                                            $cellValue = isset($userData[$cellValue]) ? $userData[$cellValue] : '';
                                        }
                                        if($header == 'approved_status'){
                                            $cellValue = ($cellValue) ? 'Approved' : 'Yet to Approve';
                                        }
                                        if($header == 'total_amount'){
                                            $cellValue = number_format($cellValue, 2);
                                        }
                                        $html .= '<td>'.$cellValue.'</td>';
                                    }
                                    $html .= '</tr>';
                                } else{
                                    $html .= '<tr>';
                                    $html .= '<td>'.$serviceUnit[$batchDetails['BatchList']['unit_code']].'</td>';
                                    foreach ($batchDetails['BatchList'] as $header => $cellValue) {
                                        if (in_array($header, array('prepared_by', 'approved_by', 'checker', 'admin_verifier', 'verified_by', 'endorse_by', 'admin_verifier'))) {
                                            $cellValue = isset($userData[$cellValue]) ? $userData[$cellValue] : '';
                                        }
                                        if($header == 'approved_status'){
                                            $cellValue = ($cellValue) ? 'Approved' : 'Yet to Approve';
                                        }
                                        if($header == 'total_amount'){
                                            $cellValue = number_format($cellValue, 2);
                                        }
                                        $html .= '<th>'.$cellValue.'</th>';
                                    }
                                    $html .= '</tr>';
                                }

                                $i++;
                            }
                            $html .='</table>';
                }
                //echo $html;
                $pdf->writeHTML($html, true, false, true, false, '');
                $pdf->lastPage();
                $pdf->Output('BatchList.pdf', 'D');
            }
        }
    }
    
    /* __________________________________________________________
     * @Method      :download_view
     * @Description :to download Payees in a Batch List
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function download_view() {
        Configure::write('debug', 0);
        $userId   = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
            
        $this->autoRender = false;
        $this->layout = '';
        if($this->request->data){
            $id = $this->request->data['Payroll']['batch_list_id'];
          
            $batchData = $this->BatchList->findById($id);

            $usersData = array();
            if(!empty($batchListData)){
                $usersIncluded = array();
                $usersIncluded[] =  $batchListData['BatchList']['prepared_by'];
                $usersIncluded[] =  $batchListData['BatchList']['verified_by'];
                $usersIncluded[] =  $batchListData['BatchList']['approved_by'];
                $usersIncluded[] =  $batchListData['BatchList']['endorse_by'];
                $usersIncluded[] =  $batchListData['BatchList']['checker'];
                $usersIncluded[] =  $batchListData['BatchList']['admin_verifier'];
                $usersIncluded = array_unique($usersIncluded);

                $fileDatas = $this->User->find('all', array(
                            'conditions' => array('User.id' => $usersIncluded),
                            'fields' => array('User.e_name', 'User.id')
                        ));
                foreach($fileDatas as $fetcheUserData){
                    $userData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
                }
            }
        
            //-------Details of User who is donloading ------------
            $this->User->recursive = -1;
            $userDetails = $this->User->findById($userId);
            
            //------------Excel File creation starts here--------------------
            if(isset($this->request->data['print_excel'])){
                $styleArray = array(
                    'font'  => array(
                        'bold'  => true,
                    ));
                $objPHPExcel = new PHPExcel(); // Excel Object
                // Set properties
                $objPHPExcel->getProperties()->setCreator($userDetails['User']['e_name']);
                $objPHPExcel->getProperties()->setLastModifiedBy($userDetails['User']['e_name']);
                $objPHPExcel->getProperties()->setTitle("Payee List");
                $objPHPExcel->getProperties()->setSubject("Payee List");
                $objPHPExcel->getProperties()->setDescription("Payee List document.");



                // Add some data
                $objPHPExcel->setActiveSheetIndex(0);
                if(!empty($batchData['Payable'])){
                    
                    $i = 0;
                    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+1))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+1),  'Batch No Id: ');
                    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+1),  $batchData['BatchList']['batch_no_id']);
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+2))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+2),  'Unit Code: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+2),  $batchData['BatchList']['unit_code']);
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+3))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+3),  'Approved By: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+3),  ($batchData['BatchList']['approved_by']) ? $userData[$batchData['BatchList']['approved_by']] : 'Yet to Approved');
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+4))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+4),  'Checked By: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+4),  ($batchData['BatchList']['checker']) ? $userData[$batchData['BatchList']['checker']] : 'Yet to Check');
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+5))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+5),  'Admin Verifier: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+5),  ($batchData['BatchList']['admin_verifier']) ? $userData[$batchData['BatchList']['admin_verifier']] : 'Admin Verify Required');
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+6))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+6),  'Month: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+6),  date('M', strtotime($batchData['BatchList']['created'])));
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+7))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+7),  'Year: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+7),  date('Y', strtotime($batchData['BatchList']['created'])));
                    
                    $objPHPExcel->getActiveSheet()->getStyle('B'.($i+8))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i+8),  'Total Amount: ');
                    $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i+8),  $batchData['BatchList']['total_amount']);
                    
                    $i = 10;
                    foreach($batchData['Payable'] as $key => $value){
                        unset($value['id']);
                        unset($value['batch_list_id']);
                        unset($value['bank_account_no']);
                        unset($value['created']);
                        unset($value['modified']);
                        if($i == 10){
                            $chr = 65;
                            foreach ($value as $header => $cellValue){
                                $objPHPExcel->getActiveSheet()->getStyle(chr($chr).$i)->applyFromArray($styleArray);
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, $header);
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).($i+1), $cellValue);
                                $chr++;
                            }
                            $i++;
                        } else{
                            $chr = 65;
                            foreach ($value as $header => $cellValue){
                                $objPHPExcel->getActiveSheet()->SetCellValue(chr($chr).$i, $cellValue);
                                $chr++;
                            }
                        }
                        $i++;
                    }
                }
                
                $objPHPExcel->getActiveSheet()->setTitle('YAN OI TONG LIMITED');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="PayeeList.xlsx"');
                header('Cache-Control: max-age=0');

                $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
                $objWriter->save('php://output');
                exit();
            }
            if(isset($this->request->data['print_pdf'])){
                //echo 'PDF Vork';
                $pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
                
                // set document information
                $pdf->SetCreator(PDF_CREATOR);
                $pdf->SetAuthor($userDetails['User']['e_name']);
                $pdf->SetTitle('YAN OI TONG LIMITED');
                //$pdf->SetSubject('Batch List');

                // set default header data
                //$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,64,255), array(0,64,128));
                //$pdf->setFooterData(array(0,64,0), array(0,64,128));

                // set header and footer fonts
                //$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
                //$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

                // set default monospaced font
                $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

                // set margins
                $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
                //$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
                //$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

                // set auto page breaks
                $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

                // set image scale factor
                $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

                // set some language-dependent strings (optional)
                if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
                    require_once(dirname(__FILE__).'/lang/eng.php');
                    $pdf->setLanguageArray($l);
                }

                // ---------------------------------------------------------

                // set default font subsetting mode
                $pdf->setFontSubsetting(true);

                // Set font
                // dejavusans is a UTF-8 Unicode font, if you only need to
                // print standard ASCII chars, you can use core fonts like
                // helvetica or times to reduce file size.
                $pdf->SetFont('kozminproregular', '', 10, '', true);

                // Add a page
                // This method has several options, check the source code documentation for more information.
                $pdf->AddPage();
                
                if (!empty($batchData['Payable'])) {
                    $i = 0;
                    $html = '<h2>YAN OI TONG LIMITED:</h2>
                            <table border="0" cellspacing="0" cellpadding="6">
                                <tr>
                                    <td style="width:60%">&nbsp;</td>
                                    <td style="width:10%">Month: </td>
                                    <td style="width:10%">' . date('M', strtotime($batchData['BatchList']['created'])) . '</td>
                                    <td style="width:10%">| Year: </td>
                                    <td style="width:10%">' . date('Y', strtotime($batchData['BatchList']['created'])) . '</td>
                                </tr>
                            </table>
                            <table border="1" cellspacing="0" cellpadding="6">
                                <tr>
                                    <th>Batch No Id</th>
                                    <th>Unit Code</th>
                                    <th>Approved By</th>
                                    <th>Checked By</th>
                                    <th>Admin Verifier</th>
                                    <th>Total Amount</th>
                                </tr>
                                <tr>
                                    <th>' . $batchData['BatchList']['batch_no_id'] . '</th>
                                    <th>' . $batchData['BatchList']['unit_code'] . '</th>
                                    <th>' . (($batchData['BatchList']['approved_by']) ? $userData[$batchData['BatchList']['approved_by']] : 'Yet to Approved') . '</th>
                                    <th>' . (($batchData['BatchList']['checker']) ? $userData[$batchData['BatchList']['checker']] : 'Yet to Check') . '</th>
                                    <th>' . (($batchData['BatchList']['admin_verifier']) ? $userData[$batchData['BatchList']['admin_verifier']] : 'Admin Verify Required') . '</th>
                                    <th>' . $batchData['BatchList']['total_amount'] . '</th>
                                </tr>
                            </table>
                            <br/>
                            <br/>
                            <table border="1" cellspacing="0" cellpadding="6">';
                            foreach ($batchData['Payable'] as $value) {
                                unset($value['id']);
                                unset($value['batch_list_id']);
                                unset($value['created']);
                                unset($value['modified']);
                                unset($value['bank_account_no']);
                                //unset($value['payment_type_id']);
                                if($i == 0){
                                    $html .= '<tr>';
                                    foreach ($value as $header => $cellValue) {
                                        $html .= '<th>'.$header.'</th>';
                                    }
                                    $html .= '</tr><tr>';
                                    foreach ($value as $header => $cellValue) {
                                        //$cellValue = ($header == 'prepared_by' || $header == 'verified_by' || $header == 'approved_by') ? $userData[$cellValue] : $cellValue ; 
                                        $html .= '<th>'.$cellValue.'</th>';
                                    }
                                    $html .= '</tr>';
                                } else{
                                    $html .= '<tr>';
                                    foreach ($value as $header => $cellValue) {
                                        //$cellValue = ($header == 'prepared_by' || $header == 'verified_by' || $header == 'approved_by') ? $userData[$cellValue] : $cellValue ; 
                                        $html .= '<th>'.$cellValue.'</th>';
                                    }
                                    $html .= '</tr>';
                                }

                                $i++;
                            }
                            $html .='</table>';
                }
                //echo $html;
                $pdf->writeHTML($html, true, false, true, false, '');
                $pdf->lastPage();
                $pdf->Output('PayeeList.pdf', 'D');                
            }
        }
    }
    

    /* __________________________________________________________
     * @Method      :changeStatus
     * @Description :to change status of a Batch List/Approve/Revert
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function changeStatus() {
        $this->autoRender = false;
        $this->layout = '';

        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');

        $id = $this->request->data['Payroll']['batch_list_id'];
        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->recursive = -1;
        $batchDetails = $this->BatchList->find('first', array('conditions' => array('BatchList.id' => $id)));

        if (isset($this->request->data['verify'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'verified_by' => $userId,
                'verified_date' => date('Y-m-d'),
            );
        }
        
        if (isset($this->request->data['endorse'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'endorse_by' => $userId,
                'endorse_date' => date('Y-m-d'),
            );
        }
        
        if (isset($this->request->data['approve']) || isset($this->request->data['approve_all'])) {
            $batchData = array();
            if ($batchDetails['BatchList']['verified_by'] == 0) {
                $batchData['BatchList']['verified_by'] = $userId;
                $batchData['BatchList']['verified_date'] = date('Y-m-d');
            } 
            if ($batchDetails['BatchList']['endorse_by'] == 0) {
                $batchData['BatchList']['endorse_by'] = $userId;
                $batchData['BatchList']['endorse_date'] = date('Y-m-d');
            } 
            
            $batchData['BatchList']['id'] = $id;
            $batchData['BatchList']['approved_by'] = $userId;
            $batchData['BatchList']['approved_date'] = date('Y-m-d');
            $batchData['BatchList']['status'] = 1;
        }

        if (isset($this->request->data['revert'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'verified_by' => 0,
                'verified_date' => '0000-00-00',
            );
        }
        
        if (isset($this->request->data['revert_endorse'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'endorse_by' => 0,
                'endorse_date' => '0000-00-00',
            );
        }

        if (isset($this->request->data['revert_approved'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'approved_by' => 0,
                'approved_date' => '0000-00-00',
            );
        }
        
        if (isset($this->request->data['revert_all'])) {
            $batchData['BatchList'] = array(
                'id' => $id,
                'approved_by' => 0,
                'approved_date' => '0000-00-00',
                'endorse_by' => 0,
                'endorse_date' => '0000-00-00',
                'verified_by' => 0,
                'verified_date' => '0000-00-00',
            );
        }

        $this->BatchList->save($batchData);
        $msg = __("UPDATE_STATUS_SUCCESS", true);
        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'view/' . $id));
    }
    
    /* __________________________________________________________
     * @Method      :delete_batch
     * @Description :to delete a Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function delete_batch($id = null, $filterMonth = null, $filterYear = null){
        $this->autoRender = false;
        $this->layout = '';
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        
        $condition = array('BatchList.id' => $id);
        $this->BatchList->delete($condition);
        
        $this->saveActionLog('batch', 'Batch Deleted with ID :'. $id);
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $payables = $this->Payable->find('list', array(
                        'fields' => array('Payable.id'), 
                        'conditions' => array('Payable.batch_list_id'  => $id)
                    ));
        $payableCondition = array('Payable.id' => $payables);
        $this->Payable->deleteAll($payableCondition);
       
        
        $filterDate = $filterYear . '-' . $filterMonth;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $filterYear . '-' . $filterMonth . '-1';
        $filterDateMax = $filterYear . '-' . $filterMonth . '-' . $maxDate;
        $batchListConditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $batchListConditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($batchListConditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('list', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :sendMail
     * @Description :to create mail form in associated batch list
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function sendMail($batch_id = null){
        $this->layout = '';

        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        $this->User->unBindModel(array('hasMany' => array('UserServiceUnit', 'Log')), false);
        $currentUserDetails = $this->User->findById($userId);
        
        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $batchData = $this->BatchList->findById($batch_id);
        
        if ($batchData) {
            App::Import("Model", "ServiceUnit");
            $this->ServiceUnit = new ServiceUnit();
            $serviceUnit = $this->ServiceUnit->find('first', array(
                'conditions' => array('ServiceUnit.unit_code' => $batchData['BatchList']['unit_code']),
                'fields' => array('ServiceUnit.id')
            ));
            
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $this->UserServiceUnit->bindModel(array(
                'belongsTo' => array(
                    'User' => array(
                        'foreignKey' => false,
                        'conditions' => 'User.id = UserServiceUnit.user_id'
                    )
                )
            ), false);
             
            $usersInServiceUnit = $this->UserServiceUnit->find('all', array(
                                    'conditions' => array(
                                        'UserServiceUnit.service_unit_id' => $serviceUnit['ServiceUnit']['id'],
                                        'User.role_id !=' => $currentUserDetails['User']['role_id'],
                                        'User.id !=' => $currentUserDetails['User']['id']
                                    ),
                                    'fields' => array('User.id', 'User.e_name', 'User.on_leave', 'User.role_id'),
                                    'order' => array('User.e_name ASC', 'User.role_id ASC')
                                    )
                                );
            
            App::Import("Model", "Role");
            $this->Role = new Role();
            $userRoles = $this->Role->find('list', array('fields' => array('Role.id', 'Role.role_name')));
            
            App::Import("Model", "Message");
            $this->Message = new Message();
            $messageOptions = $this->Message->find('list');
        }
        
        $this->set('messageOptions', $messageOptions);
        $this->set('usersInServiceUnit', $usersInServiceUnit);
        $this->set('userRoles', $userRoles);
        $this->set('batchData', $batchData);
        $this->set('currentUserDetails', $currentUserDetails);
    }
    
    /* __________________________________________________________
     * @Method      :process_mail
     * @Description :to send mail to users in associated batch list
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function process_mail(){
        $this->layout = '';
        $this->autoRender = false;
        $userId = $current_user_id = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');

        if ($this->request->data) {
            $batch_id = $this->request->data['Payroll']['batch_list_id'];
            $remark = $this->request->data['Payroll']['remark'];
            $seletedMailReceiverArray = $this->request->data['Payroll']['receiver'];
            
            $seletedMailReceiver = array();
            if(!empty($seletedMailReceiverArray)){
                foreach($seletedMailReceiverArray as $mailReceiver){
                    if($mailReceiver)
                    $seletedMailReceiver[] = $mailReceiver;
                }
            }
            
            App::Import("Model", "Message");
            $this->Message = new Message();
            $messageDetail = $this->Message->findById($this->request->data['Payroll']['message'], array('Message.title'));
        
            App::Import("Model", "BatchList");
            $this->BatchList = new BatchList();
            $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
            $this->BatchList->bindModel(array(
                'belongsTo' => array(
                    'ServiceUnit' => array(
                        'foreignKey' => false,
                        'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                    )
                )
            ), false);
            $batchData = $this->BatchList->findById($batch_id);
            
            $usersData = $userAttachedToUnit = array();
            //$userAttachedToUnit[0] = $userId;
            if ($batchData) {
                App::Import("Model", "UserServiceUnit");
                $this->UserServiceUnit = new UserServiceUnit();
                $userServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.service_unit_id' => $batchData['ServiceUnit']['id']),
                                        'fields' => array('UserServiceUnit.user_id'))
                                    );

                if (!empty($userServiceUnit)) {
                    foreach ($userServiceUnit as $includedUserId) {
                        $userAttachedToUnit[] = $includedUserId;
                    }
                }
            }
            
            $userAttachedToUnit = array_unique($userAttachedToUnit);
            $this->User->unBindModel(array('hasMany' => array('UserServiceUnit')), false);
            $userDataFetched = $this->User->find('all', array(
                'conditions' => array('User.id' => $userAttachedToUnit),
                'fields' => array('User.e_name', 'User.id', 'User.email', 'User.on_leave', 'User.role_id', 'Role.role_name')
            ));
            
            $userEmail = $usersData = $userOnLeave = $userRole = array();
            if(!empty($userDataFetched)){
                foreach($userDataFetched as $attachedUser){
                    $userEmail[$attachedUser['User']['id']] = $attachedUser['User']['email'];
                    $usersData[$attachedUser['User']['id']] = $attachedUser['User']['e_name'];
                    $userRole[$attachedUser['User']['id']] = $attachedUser['Role']['role_name'];
                    if ($attachedUser['User']['on_leave'] == 1) {
                        $userOnLeave[] = $attachedUser['User']['id'];
                    }
                }
            }

            $mail = array();
            $toUser = $ccUser = $allUser = $toUserEmails =  $ccUserEmails = $allUserEmail = array();
            if(!empty($userAttachedToUnit)){
                $mail = array('Mail' => array(
                            'mail_type' => $batchData['ServiceUnit']['unit_code'].' part time Payroll Status', 
                            'sent_by' => $userId,
                            'message_id' => $this->request->data['Payroll']['message'],
                            'remark' => $remark)
                        );
                foreach($userAttachedToUnit as $userToUnit){
                    if(in_array($userToUnit, $seletedMailReceiver)){
                        $toUserEmails[] = $userEmail[$userToUnit];
                        $mail['UserMail'][] = array('user_id' => $userToUnit, 'mail_type' => 'to');
                    } 
                }
            }
            
            App::Import("Model", "Mail");
            $this->Mail = new Mail();
            if(!empty($mail)){
                $this->Mail->saveAssociated($mail, array('deep' => true));
                $this->saveActionLog('batch', 'Saved Mail History for Batch Id :'. $batch_id);
            } 
            
            
            $currentUser = isset($usersData[$current_user_id]) ? $usersData[$current_user_id] : 'Admin';
            $msg = '<table cellpadding="5">
                        <tr>
                            <td style="text-align: right; font-weight: bold;">'. __('FROM'). ' :</td>
                            <td>' . (isset($usersData[$current_user_id]) ? $usersData[$current_user_id]: 'Admin') . '</td>
                            <td>' . __('ROLE'). ' :</td>
                            <td>' . $currentUser . '</td>
                        </tr>
                        <tr>
                            <td colspan="4">&nbsp;</td>
                        </tr>
                        <tr>
                            <td style="text-align: right; font-weight: bold;">'. __('TO') . ' :</td>
                            <td>' .  __('RECIPIENTS') . '  </td>
                            <td>' .  __('ROLE') . ' </td>
                            <td>' .  __('LEAVE_STATUS') . ' </td>
                        </tr>';
            if(!empty($seletedMailReceiver)){ foreach($seletedMailReceiver as $users){ 
                $msg .= '<tr>
                            <td>&nbsp;</td>
                            <td>' . $usersData[$users] . '</td>
                            <td>' . $userRole[$users] . '</td>
                            <td>' . ((in_array($users, $userOnLeave)) ? __('ON_LEAVE') : '' ). '</td>
                        </tr>';
            } }
                $msg .= '<tr>
                            <td colspan="4">&nbsp;</td>
                        </tr>
                        <tr>
                            <td style="text-align: right; font-weight: bold;">'. __('SUBJECT'). ' :</td>
                            <td colspan="3">' .$batchData['ServiceUnit']['unit_code']. ' part time payroll (from ' . $currentUser . ')</td>
                        </tr>
                        <tr>
                            <td style="text-align: right; font-weight: bold;">' . __('MESSAGE') . ' :</td>
                            <td colspan="3">' .$messageDetail['Message']['title']. '</td>
                        </tr>
                        <tr>
                            <td style="text-align: right; font-weight: bold;">' . __('REMARK'). ' :</td>
                            <td colspan="3">' . $remark . ' </td>
                        </tr>
                    </table>';
                
            $subject = $batchData['ServiceUnit']['unit_code']. ' part time payroll (from ' . $currentUser . ')';
            //--------- Mail Chain----------------
            $Email = new CakeEmail();
            $Email->config('gmail');
            if(empty($toUserEmails)){
                $toUserEmails[] = 'slsahani@gmail.com'; 
            }
            $toUserEmails = array_unique($toUserEmails);
            $fromArray = array(
                (isset($userEmail[$userId]) ? $userEmail[$userId] : 'admin@probano.com') => (isset($usersData[$userId]) ? $usersData[$userId] : 'admin@probano.com')
            );
            $Email->from($fromArray)
                    ->to($toUserEmails)
                    ->emailFormat('both')
                    ->subject($subject)
                    ->send($msg);
            
            $this->saveActionLog('batch', 'Mail Sent for Batch Id :'. $batch_id);
            
            $msg = __("MAIL_STATUS_SUCCESS", true);
            $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
            return $this->redirect(array('action' => 'view/'.$batch_id));
        }
    }
    
    /* __________________________________________________________
     * @Method      :email_history
     * @Description :to show email history to logged in user
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function email_history() {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Mail");
        $this->Mail = new Mail();
        
        App::Import("Model", "User");
        $this->User = new User();
        
        if ($this->request->data) {
            //pr($this->request->data);
            $filterDate = $filterDateMin = $filterDateMax = '';
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] != ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
            }
            
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] == ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-1-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-12-31';
            }
            
            $conditions = array(
                'Mail.created >= ' => $filterDateMin,
                'Mail.created <= ' => $filterDateMax
            );
            //echo 'Date Min'. $filterDateMin;
            //echo '<br>Date Maxs'. $filterDateMax;
        } else{
            $filterDateMin = date("Y-m-1");
            $filterDateMax = date("Y-m-t");
            $conditions = array(
                'Mail.created >= ' => $filterDateMin,
                'Mail.created <= ' => $filterDateMax
            );
        }

        $mailData = $this->paginate('Mail', array($conditions));
        $userInvolved = array();
        if(!empty($mailData)){
            foreach($mailData as $mails){
                $userInvolved[] = $mails['Mail']['sent_by'];
            }
            $userInvolved = array_unique($userInvolved);
        }
        $userData = $this->User->find('list', array(
                    'conditions' => array('User.id' => $userInvolved),
                    'fields' => array('User.id', 'User.e_name')
                ));
      
        $this->set('mailData', $mailData);
        $this->set('userData', $userData);
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('email_list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :view_email_history
     * @Description :to show email history to logged in user
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function view_email_history($mailId = null) {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "UserMail");
        $this->UserMail = new UserMail();
        
        $conditions = array('UserMail.mail_id' => $mailId);
        $mailData = $this->paginate('UserMail', array($conditions));

        $userInvolved = array();
        if(!empty($mailData)){
            foreach($mailData as $mails){
                $userInvolved[] = $mails['UserMail']['user_id'];
            }
            $userInvolved = array_unique($userInvolved);
        }
        $userData = $this->User->find('list', array(
                    'conditions' => array('User.id' => $userInvolved),
                    'fields' => array('User.id', 'User.e_name')
                ));
      
        $this->set('mailData', $mailData);
        $this->set('userData', $userData);
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('email_history_list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :check_email_history
     * @Description :to show email history to logged in user
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function check_email_history($id = NULL) {
        $this->layout = 'pop_up_window';
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", array("Mail", "UserMail"));
        $this->Mail = new Mail();
        $this->UserMail = new UserMail();
        $this->UserMail->unBindModel(array('belongsTo' => array('Mail')), false);
        $this->UserMail->bindModel(array(
            'belongsTo' => array(
                'User' => array( )
            )
        ), false);
        
        $this->Mail->recursive = 2;
        $mailData = $this->Mail->findById($id);
        
        App::Import("Model", "User");
        $this->User = new User();
        $currentUserDetails = $this->User->findById($mailData['Mail']['sent_by']);
        
        App::Import("Model", "Role");
        $this->Role = new Role();
        $userRoles = $this->Role->find('list', array('fields' => array('Role.id', 'Role.role_name')));
        App::Import("Model", "Message");
        $this->Message = new Message();
        $messageOptions = $this->Message->find('list');
        
        $this->set('messageOptions', $messageOptions);
        $this->set('mailData', $mailData);
        $this->set('userRoles', $userRoles);
        $this->set('currentUserDetails', $currentUserDetails);
    }
    
    /* __________________________________________________________
     * @Method      :hold_payment
     * @Description :To hold payable payment
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function hold_payment($payable_id = null, $batch_id = null){
        $this->layout = '';
        $this->autoRender = false;
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        
        $payable = array();
        $payable['Payable']['id'] = $payable_id;
        $payable['Payable']['on_hold'] = 1;
        
        $this->Payable->save($payable);
        
        $this->saveActionLog('batch', 'Hold Payment for Payable ID :'. $payable_id);
        
        $msg = __("UPDATE_STATUS_SUCCESS", true);
        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'view/' . $batch_id));
    }
    
    /* __________________________________________________________
     * @Method      :hold_payment
     * @Description :To hold payable payment
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function revert_hold_payment($payable_id = null, $batch_id = null){
        $this->layout = '';
        $this->autoRender = false;
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        
        $payable = array();
        $payable['Payable']['id'] = $payable_id;
        $payable['Payable']['on_hold'] = 0;
        
        $this->Payable->save($payable);
        
        $this->saveActionLog('batch', 'Revert Hold Payment for Payable ID :'. $payable_id);
        
        $msg = __("UPDATE_STATUS_SUCCESS", true);
        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'view/' . $batch_id));
    }
    
    /* __________________________________________________________
     * @Method      :admin_hold_payment
     * @Description :To hold payable payment
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_hold_payment($payable_id = null, $batch_id = null){
        $this->layout = '';
        $this->autoRender = false;
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        
        $payable = array();
        $payable['Payable']['id'] = $payable_id;
        $payable['Payable']['on_hold'] = 1;
        
        $this->Payable->save($payable);
        
        $this->saveActionLog('batch', 'Hold Payment for Payable ID :'. $payable_id);
        
        $msg = __("UPDATE_STATUS_SUCCESS", true);
        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'admin_verification_view/' . $batch_id));
    }
    
    /* __________________________________________________________
     * @Method      :admin_revert_hold_payment
     * @Description :To hold payable payment
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_revert_hold_payment($payable_id = null, $batch_id = null){
        $this->layout = '';
        $this->autoRender = false;
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        
        $payable = array();
        $payable['Payable']['id'] = $payable_id;
        $payable['Payable']['on_hold'] = 0;
        
        $this->Payable->save($payable);
        
        $this->saveActionLog('batch', 'Revert Hold Payment for Payable ID :'. $payable_id);
        
        $msg = __("UPDATE_STATUS_SUCCESS", true);
        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'admin_verification_view/' . $batch_id));
    }
    
    /* __________________________________________________________
     * @Method      :edit_payable
     * @Description :for limited edit on Payable
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function edit_payable($id = null){
        $this->layout = 'pop_up_window';
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        if (!$id) {
            throw new NotFoundException(__('INVALID_PAYEE'));
        }
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        
        $payable = $this->Payable->findById($id);

        if (!$payable) {
            throw new NotFoundException(__('INVALID_PAYEE'));
        }
        
        if ($this->request->is(array('Payable', 'put'))) {
            $this->Payable->id = $id;
            $batch_list_id = $this->request->data['Payable']['batch_list_id'];
            if ($this->Payable->save($this->request->data)) {
                
                $this->Payable->virtualFields = array('total' => 'SUM(Payable.amount_payable)');
                $payableTotal = $this->Payable->find('first', array(
                        'conditions' => array('Payable.batch_list_id' => $batch_list_id),
                        'fields' => array('Payable.total')
                    ));
               
                App::Import("Model", "BatchList");
                $this->BatchList = new BatchList();
                $batchData = array();
                $batchData['BatchList']['id'] = $batch_list_id;
                $batchData['BatchList']['total_amount'] = $payableTotal['Payable']['total'];
                $this->BatchList->save($batchData);
        
                $msg = __("PAYEE_UPDATE_SUCCESS", true);
                $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
                return $this->redirect(array('action' => 'admin_verification_view/' . $batch_list_id));
            }
            $msg = __("PAYEE_UPDATE_FAILURE", true);
            $this->Session->setFlash('<div class="totalErrors">' . $msg . '</div>');
            return $this->redirect(array('action' => 'view/' . $batch_list_id));
        }
        if (!$this->request->data) {
            $this->request->data = $payable;
            $this->request->data['Payable']['batch_list_id'] = $payable['BatchList']['id'];
        }
    }
    /* __________________________________________________________
     * @Method      :Index
     * @Description :To show list of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_index() {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        
        $savecrit = '';

        //criteria array for search
        $crit = array();
        $crit[] = "";
        
        $filterMonth = $filterYear = '';
        $conditions = array();
        if ($this->request->data) {
            //pr($this->request->data);
            $filterDate = $filterDateMin = $filterDateMax = '';
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] != ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
                
                $filterMonth = $this->request->data['Payroll']['month'];
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] == ''){
                $filterDateMin = $this->request->data['Payroll']['year'].'-1-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-12-31';
                
                $filterMonth = 1;
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if ($this->request->data['Payroll']['search_criteria'] != '' && $filterDateMin == '' && $filterDateMax == '') {
                $searchCriteriaTerm = trim($this->data['Payroll']['search_criteria']);
                //will search with name and abbreviation
                //$crit[] = "(BatchList.unit_code like '%" . $searchCriteriaTerm . "%')";
                $conditions = array('BatchList.unit_code like' => "%" . $searchCriteriaTerm . "%");

                //$savecrit .= "search_criteria:" . $searchCriteriaTerm . "/";
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->data['Payroll']['search_criteria'] == '') {
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->request->data['Payroll']['search_criteria'] != '') {
                $searchCriteriaTerm = trim($this->request->data['Payroll']['search_criteria']);
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax,
                    'BatchList.unit_code LIKE ' => "%" . $searchCriteriaTerm . "%",
                );
            }
            //echo 'Date Min'. $filterDateMin;
            //echo '<br>Date Maxs'. $filterDateMax;
        } else{
            $filterDateMin = date("Y-m-1");
            $filterDateMax = date("Y-m-t");
            $conditions = array(
                'BatchList.prepared_date >= ' => $filterDateMin,
                'BatchList.prepared_date <= ' => $filterDateMax
            );
            
            $filterMonth = date("m");
            $filterYear = date("Y");
        }
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $conditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($conditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }

        /*$batchDetails = $batchData;
        $newBatachData = $tempArray = array();
        if(!empty($batchDetails)){
            foreach($batchDetails as $key => $details){
                foreach($details['BatchList'] as $subKey => $value){
                    $tempArray['BatchList'][$subKey] = $value;
                    if($subKey == 'unit_code'){
                        $tempArray['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
                    }
                }
                $newBatachData[$key] = $tempArray; 
                $newBatachData[$key]['BatchList']['payable_count'] = count($details['Payable']); 
                //$batchData[$key]['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
            }
        }*/
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :view
     * @Description :To view details of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_view($id  = null) {
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        
        if (!$id) {
            throw new NotFoundException(__('INVALID_PAYROLL'));
        }
        $this->BatchList->recursive = -1;
        $batchListData = $this->BatchList->findById($id);
        
        $this->Payable->recursive = -1;
        $this->Payable->virtualFields = array('amount' => 'ROUND(Payable.amount_payable)');
        $condition = array('Payable.batch_list_id'  => $id);
        $payableList = $this->paginate('Payable', array($condition));
        
        $usersData = array();
        if(!empty($batchListData)){
            $usersIncluded = array();
            $usersIncluded[] =  $batchListData['BatchList']['prepared_by'];
            $usersIncluded[] =  $batchListData['BatchList']['verified_by'];
            $usersIncluded[] =  $batchListData['BatchList']['approved_by'];
            $usersIncluded[] =  $batchListData['BatchList']['endorse_by'];
            $usersIncluded[] =  $batchListData['BatchList']['checker'];
            $usersIncluded[] =  $batchListData['BatchList']['admin_verifier'];
            $usersIncluded = array_unique($usersIncluded);
            
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }
        
        $this->set('payableList', $payableList);
        $this->set('usersData', $usersData);
        
        if (!$batchListData) {
            throw new NotFoundException(__('INVALID_BATCH'));
        } else{
            $this->set('batchListData', $batchListData);
        }
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_payable_list', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :adminUploadBatch
     * @Description :To view details of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function adminUploadBatch() {
        Configure::write('debug', 0);
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        $this->autoRender = false;
        $this->layout = '';
        
        if ($this->request->is('post')) {
            $this->Payable->create();     

            $this->Payable->set($this->request->data['Payroll']);
            if ($this->Payable->validates()) {
                $tmp_name = $this->request->data['Payroll']["excel_file"]["tmp_name"];
                $name = $this->request->data['Payroll']["excel_file"]["name"];
                $newFileName = date('H_i').'_'.str_replace(" ", "_", $name);
                
                $dateFolder = date('M_d_Y');
                $uploadDir = UPLOAD_EXCEL_DIR.DS.$dateFolder;
                //--------- Directory Creation------------
                if (!is_dir($uploadDir)) {
                    mkdir($uploadDir, 0777);         
                }
                if(move_uploaded_file($tmp_name, $uploadDir.DS.$newFileName)){
                    $uploadedFile = $uploadDir.DS.$newFileName;
                    if (!is_readable($uploadedFile)) {
                        $this->error( 'File not found' );
                        return false;
                    }
                    
                    App::Import("Model", "PaymentType");
                    $this->PaymentType = new PaymentType();
                    $paymentTypes = $this->PaymentType->find('list', array('fields' => array('PaymentType.id')));
                    //pr($paymentTypes);
                    
                    App::Import("Model", "Payee");
                    $this->Payee = new Payee();
                    $payees = $this->Payee->find('list', array('fields' => array('Payee.id', 'Payee.payee_id_proof_no')));
                    
                    App::Import("Model", "Payable");
                    $this->Payable = new Payable();
                    $payables = $this->Payable->find('list', array('fields' => array('Payable.id', 'Payable.payee_id_proof_no')));
                    
                    App::Import("Model", "ServiceUnit");
                    $this->ServiceUnit = new ServiceUnit();
                    $serviceUnit = $this->ServiceUnit->find('list', array('fields' => array('ServiceUnit.id', 'ServiceUnit.unit_code')));
                    
                    /*App::Import("Model", "BatchList");
                    $this->BatchList = new BatchList();
                    $batchLists = $this->BatchList->find('list', array(
                                    'conditions' => array('BatchList.approved_status' => 0), 
                                    'fields' => array('BatchList.id', 'BatchList.batch_no_id')
                                ));*/
                   // pr($batchLists);
                    
                    //---------- Parsing Start--------------------
                    $inputFileType = PHPExcel_IOFactory::identify($uploadedFile);
                    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                    $objReader->setReadDataOnly(true);
                    $objPHPExcel = $objReader->load($uploadedFile);
                    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
                    $highestRow = $objWorksheet->getHighestRow();
                    $highestColumn = $objWorksheet->getHighestColumn();
                    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
                    
                    $paymentTypeIdArray = $payeeIdArray = $batchIdArray = $payeesData = $columnNameArray = array();
                    $paymentTypeIdColumn = $payeeIdColumn = $batchNoIdColumn = $unitCodeColumn = $errors = '';
                    
                    $firstColumn = $objWorksheet->getCellByColumnAndRow(0, 1)->getValue();
                    
                    //if($firstColumn == 'id'){
                        $grandTotalIncome = 0;
                        for ($row = 1; $row <= $highestRow;  ++$row) {
                            for ($col = 0; $col < $highestColumnIndex;  ++$col) {
                                $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                                $arraydata[$row - 1][$col] = $value;
                                /*if($value == 'unit_code' && $unitCodeColumn == ''){
                                    $unitCodeColumn = $col;
                                }*/
                                if($value == 'payment_type_id' && $paymentTypeIdColumn == ''){
                                    $paymentTypeIdColumn = $col;
                                }
                                /*if($value == 'payee_id_proof_no' && $payeeIdColumn == ''){
                                    $payeeIdColumn = $col;
                                }*/
                                if($row == 1){
                                    $columnNameArray[$value] = $col;
                                }
                            }
                            //pr($columnNameArray);

                            if($row > 1){
                                $unitCodeVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['unit_code'], $row)->getValue();
                                $paymentTypeIdVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_type_id'], $row)->getValue();
                                $eNameVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getValue();
                                $payeeIdProofVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getValue();
                                $dateBeginVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_begin'], $row)->getValue();
                                $dateEndVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_end'], $row)->getValue();
                                $amtPayableVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getValue();
                                //$bankAccountNoVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['bank_account_no'], $row)->getValue();
                                $MPFVal = $objWorksheet->getCellByColumnAndRow($columnNameArray['MPF'], $row)->getCalculatedValue();
                                
                                if(($unitCodeVal != '') && ($paymentTypeIdVal != '') && ($eNameVal != '') && ($payeeIdProofVal != '') && ($dateBeginVal != '') && ($dateEndVal != '') && ($MPFVal != '')){
                                    if($row == 2){
                                        $unit_code = $objWorksheet->getCellByColumnAndRow($columnNameArray['unit_code'], $row)->getValue();
                                        $batchData['BatchList'] = array(
                                            //'batch_no_id' => $objWorksheet->getCellByColumnAndRow($columnNameArray['batch_no_id'], $row)->getValue(),
                                            'batch_no_id' => '',
                                            'unit_code' => $unit_code,
                                            'prepared_by' => $userId,
                                            'prepared_date' => date('Y-m-d'),
                                            'approved_status' => 0
                                        );
                                        if(!in_array($unit_code, $serviceUnit)){
                                            $errors .= __('UNIT_CODE'). ' ' . $unit_code . ' ' .__('IS_NOT_AVAILABLE').'<br>';
                                        }
                                    }

                                    $payeeIdProofNo = $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getValue();
                                    if(!in_array($payeeIdProofNo, $payees)){
                                        $payeesUserData[]['Payee'] = array(
                                            'payee_id_proof_no' => $payeeIdProofNo,
                                            'eng_name' => $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getValue(),
                                            'chi_name' => $objWorksheet->getCellByColumnAndRow($columnNameArray['c_name'], $row)->getValue(),
                                            'email' => '',
                                            'phone' => '',
                                            'recorded_by' => $userId,
                                            'last_created_by' => $userId
                                        );
                                        $payees[] = $payeeIdProofNo;
                                    }

                                    $phpDateValueBegin = $phpDateValueEnd = time();
                                    if(isset($columnNameArray['date_begin'])){
                                        $excelDateValueBegin = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_begin'], $row)->getValue();
                                        $phpDateValueBegin = PHPExcel_Shared_Date::ExcelToPHP($excelDateValueBegin);
                                    }

                                    if(isset($columnNameArray['date_end'])){
                                        $excelDateValueEnd = $objWorksheet->getCellByColumnAndRow($columnNameArray['date_end'], $row)->getValue();
                                        $phpDateValueEnd = PHPExcel_Shared_Date::ExcelToPHP($excelDateValueEnd);
                                    }

                                    $payeesData[]['Payable'] = array(
                                        //'payee_id' => $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id'], $row)->getValue(),
                                        'payment_type_id' => isset($columnNameArray['payment_type_id']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_type_id'], $row)->getCalculatedValue() : '',
                                        'c_name' => isset($columnNameArray['c_name']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['c_name'], $row)->getCalculatedValue() : '',
                                        'e_name' => isset($columnNameArray['e_name']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['e_name'], $row)->getCalculatedValue() : '',
                                        'payee_id_proof_no' => isset($columnNameArray['payee_id_proof_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payee_id_proof_no'], $row)->getCalculatedValue() : '',
                                        'payment_subject' => isset($columnNameArray['payment_subject']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['payment_subject'], $row)->getCalculatedValue() : '',
                                        'payment_begin' =>  date('Y-m-d', $phpDateValueBegin),
                                        'payment_end' => date('Y-m-d', $phpDateValueEnd),
                                        'course_head_count' => isset($columnNameArray['head_cnt']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['head_cnt'], $row)->getCalculatedValue() : '',
                                        'course_fee' => isset($columnNameArray['course_fee']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['course_fee'], $row)->getCalculatedValue() : '',
                                        'total_fee_income' => isset($columnNameArray['total_income']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['total_income'], $row)->getCalculatedValue() : '',
                                        'income_splite_remarks' => isset($columnNameArray['remark_share']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['remark_share'], $row)->getCalculatedValue() : '',
                                        'course_remarks' => isset($columnNameArray['remark_gen']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['remark_gen'], $row)->getCalculatedValue() : '',
                                        'amount_payable' => isset($columnNameArray['amt_payable']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getCalculatedValue() : '',
                                        'bank_account_no' => isset($columnNameArray['bank_acct_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['bank_acct_no'], $row)->getCalculatedValue() : '',
                                        'mpf_entitlement' => isset($columnNameArray['MPF']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['MPF'], $row)->getCalculatedValue() : '',
                                        //'chk418' => isset($columnNameArray['chk418']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['chk418'], $row)->getCalculatedValue() : '',
                                        //'chkreserve' => isset($columnNameArray['chkreserve']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['chkreserve'], $row)->getCalculatedValue() : '',
                                        'contract_sheet_no' => isset($columnNameArray['contract_sheet_no']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['contract_sheet_no'], $row)->getCalculatedValue() : '',
                                    );
                                    $payables[] = $payeeIdProofNo;
                                    $grandTotalIncome += isset($columnNameArray['amt_payable']) ? $objWorksheet->getCellByColumnAndRow($columnNameArray['amt_payable'], $row)->getCalculatedValue() : 0 ;
                                } else {
                                    if($unitCodeVal == ''){$errors .= 'unit_code ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($paymentTypeIdVal == ''){$errors .= 'payment_type_id ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($eNameVal == ''){$errors .= 'e_name ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($payeeIdProofVal == ''){$errors .= 'payee_id_proof_no ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($dateBeginVal == ''){$errors .= 'date_begin ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($dateEndVal == ''){$errors .= 'date_end ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($amtPayableVal == ''){$errors .= 'amt_payable ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    //if($bankAccountNoVal == ''){$errors .= 'bank_acct_no ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                    if($MPFVal == ''){$errors .= 'MPF ' . __('COLUMN_CANNOT_EMPTY_AT_ROW') . ' ' . $row .'<br>';}
                                }
                            }


                            if($paymentTypeIdColumn){
                                $paymentTypeId = $objWorksheet->getCellByColumnAndRow($paymentTypeIdColumn, $row)->getValue();
                                if(($paymentTypeId)  && ($paymentTypeId != 'payment_type_id')){
                                    $paymentTypeIdArray[] = $paymentTypeId;
                                    if(!in_array($paymentTypeId, $paymentTypes)){
                                        $errors .= __('PAYMENT_TYPE_ID'). ' ' . $paymentTypeId . ' ' .__('IS_NOT_AVAILABLE').'<br>';
                                    }
                                }
                            }
                        }
                    /*} else{
                        $errors .= __('FIRST_COLUMN_NAME_ERROR');
                    }*/
                   
                    if(empty($errors)){
                        /*if(!empty($batchIdArrayToDelete)){
                            $condition = array('BatchList.id' => $batchIdArrayToDelete);
                            $this->BatchList->deleteAll($condition, FALSE);
                        }*/
                        $this->Payee->validator()->remove('eng_name');
                        $this->Payee->validator()->remove('chi_name');
                        $this->Payee->validator()->remove('phone');
                        $this->Payee->validator()->remove('email');
                        $this->Payee->validator()->remove('bank_account_no');
                        if(!empty($payeesUserData)){
                            $this->Payee->saveMany($payeesUserData);
                        }
                        $payees = $this->Payee->find('list', array('fields' => array('Payee.id', 'Payee.payee_id_proof_no')));
                        
                        $batchData['BatchList']['total_amount'] = $grandTotalIncome;
                        $this->BatchList->save($batchData);
                        $batchId = $this->BatchList->getLastInsertID();
                        
                        //----------Payables Data-----------
                        $payeesDataNew = array();
                        foreach($payeesData as $retrivedData){
                            $retrivedData['Payable']['batch_list_id'] = $batchId;
                            //$retrivedData['Payable']['payee_id_proof_no'] = array_search($retrivedData['Payable']['payee_id_proof_no'], $payees);
                            $payeesDataNew[] = $retrivedData;
                        }
                        unset($payeesDataNew[0]);
                        $this->Payable->saveMany($payeesDataNew);
                        $msg = __("FILE_UPLOAD_SUCCESS", true);
                        $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
                        return $this->redirect(array('action' => 'admin_verification_index'));
                    } else{
                        $this->Session->setFlash('<div class="totalErrors">' . $errors . '</div>');
                        
                        return $this->redirect(array('action' => 'admin_verification_index'));
                    }
                }
               
            } else {
                $msg = __("INVALID_FILE_TYPE", true). ' ' .__("ONLY_XLS_ALLOWED", true);
                $this->Session->setFlash('<div class="totalErrors">' . $msg . '</div>');
                return $this->redirect(array('action' => 'admin_verification_index'));
            }
        }
    }
    
    /* __________________________________________________________
     * @Method      :changeStatus
     * @Description :to change status of a Batch List/Approve/Revert
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function adminChangeStatus() {
        $this->autoRender = false;
        $this->layout = '';
        
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        
        $id = $this->request->data['Payroll']['batch_list_id'];
        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->recursive =-1;
        $batchDetails = $this->BatchList->find('first', array('conditions' => array('BatchList.id' => $id)));
        
        if(isset($this->request->data['verify'])){
            $batchData['BatchList'] = array(
                                'id' => $id,
                                'verified_by' => $userId,
                                'verified_date' => date('Y-m-d'),
                            );
        }
        if(isset($this->request->data['approve'])){
            if($batchDetails['BatchList']['verified_by'] == 0){
                $batchData['BatchList'] = array(
                                'id' => $id,
                                'verified_by' => $userId,
                                'verified_date' => date('Y-m-d'),
                                'approved_by' => $userId,
                                'approved_date' => date('Y-m-d'),
                                'status' => 1,
                            );
            } else{
                $batchData['BatchList'] = array(
                                'id' => $id,
                                'approved_by' => $userId,
                                'approved_date' => date('Y-m-d'),
                                'status' => 1,
                            );
            }
        }
        
        if(isset($this->request->data['revert'])){
            $batchData['BatchList'] = array(
                                'id' => $id,
                                'verified_by' => 0,
                                'verified_date' => '0000-00-00',
                            );
        }
        
        if(isset($this->request->data['revert_approved'])){
            $batchData['BatchList'] = array(
                                'id' => $id,
                                'approved_by' => 0,
                                'approved_date' => '0000-00-00',
                            );
        }
        
        $this->BatchList->save($batchData);
        $msg = __("UPDATE_STATUS_SUCCESS", true);
	$this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'admin_view/'.$id));
    }
    
    /* __________________________________________________________
     * @Method      :delete_batch
     * @Description :to delete a Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_delete_batch($id = null, $filterMonth = null, $filterYear = null){
        $this->autoRender = false;
        $this->layout = '';
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        $condition = array('BatchList.id' => $id);
        $this->BatchList->delete($condition);
        
        $this->saveActionLog('batch', 'Batch Deleted with ID :'. $id);
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $payables = $this->Payable->find('list', array(
                        'fields' => array('Payable.id'), 
                        'conditions' => array('Payable.batch_list_id'  => $id)
                    ));
        $payableCondition = array('Payable.id' => $payables);
        $this->Payable->deleteAll($payableCondition);
        
        $filterDate = $filterYear . '-' . $filterMonth;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $filterYear . '-' . $filterMonth . '-1';
        $filterDateMax = $filterYear . '-' . $filterMonth . '-' . $maxDate;
        $batchListConditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $batchListConditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($batchListConditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_list', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :delete_batch
     * @Description :to delete a Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_verification_delete_batch($id = null, $filterMonth = null, $filterYear = null){
        $this->autoRender = false;
        $this->layout = '';
        
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        $condition = array('BatchList.id' => $id);
        $this->BatchList->delete($condition);
        
        $this->saveActionLog('batch', 'Batch Deleted with ID :'. $id);
        
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $payables = $this->Payable->find('list', array(
                        'fields' => array('Payable.id'), 
                        'conditions' => array('Payable.batch_list_id'  => $id)
                    ));
        $payableCondition = array('Payable.id' => $payables);
        $this->Payable->deleteAll($payableCondition);
        
        $filterDate = $filterYear . '-' . $filterMonth;
        $maxDate = date("t", strtotime($filterDate));
        $filterDateMin = $filterYear . '-' . $filterMonth . '-1';
        $filterDateMax = $filterYear . '-' . $filterMonth . '-' . $maxDate;
        $batchListConditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $batchListConditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($batchListConditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_verification_list', 'ajax');
        }
    }
    
    /* __________________________________________________________
     * @Method      :sendMail
     * @Description :to create mail form in associated batch list
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function sendMailVerification($batch_id = null){
        $this->layout = '';

        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');

        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->recursive = -1;
        $batchData = $this->BatchList->findById($batch_id);
                
        $usersData = $userAttachedToUnit = array();
        $usersIncluded[0] = $userId;

        if ($batchData) {
            App::Import("Model", "ServiceUnit");
            $this->ServiceUnit = new ServiceUnit();
            $serviceUnit = $this->ServiceUnit->find('first', array(
                'conditions' => array('ServiceUnit.unit_code' => $batchData['BatchList']['unit_code']),
                'fields' => array('ServiceUnit.id')
            ));

            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $userServiceUnit = $this->UserServiceUnit->find('list', array(
                                    'conditions' => array('UserServiceUnit.service_unit_id' => $serviceUnit['ServiceUnit']['id']),
                                    'fields' => array('UserServiceUnit.user_id'))
                                );
            
            if (!empty($userServiceUnit)) {
                foreach ($userServiceUnit as $includedUserId) {
                    $usersIncluded[] = $includedUserId;
                    $userAttachedToUnit[] = $includedUserId;
                }
            }
        }
        
        if (!empty($batchData)) {
            //foreach ($batchData as $fetcheddData){
            $usersIncluded[] = $batchData['BatchList']['prepared_by'];
            $usersIncluded[] = $batchData['BatchList']['verified_by'];
            $usersIncluded[] = $batchData['BatchList']['approved_by'];
            $usersIncluded[] = $batchData['BatchList']['endorse_by'];
            //}
            $usersIncluded = array_unique($usersIncluded);
        }
        $this->User->recursive = -1;
        $userDataFetched = $this->User->find('all', array(
            'conditions' => array('User.id' => $usersIncluded),
            'fields' => array('User.e_name', 'User.id', 'User.on_leave')
        ));
        
        $userOnLeave = array();
        foreach ($userDataFetched as $fetchedUserData) {
            $usersData[$fetchedUserData['User']['id']] = $fetchedUserData['User']['e_name'];
            if($fetchedUserData['User']['on_leave'] == 1){
                $userOnLeave[] = $fetchedUserData['User']['id'];
            }
        }
        
        $this->set('userAttachedToUnit', $userAttachedToUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        $this->set('userOnLeave', $userOnLeave);
        $this->set('current_user_id', $userId);
    }
    
    /* __________________________________________________________
     * @Method      :process_mail
     * @Description :to send mail to users in associated batch list
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function process_mail_verification(){
        $this->layout = '';
        $this->autoRender = false;
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        if ($this->request->data) {
            $batch_id = $this->request->data['Payroll']['batch_list_id'];
            $remark = $this->request->data['Payroll']['remark'];
            App::Import("Model", "BatchList");
            $this->BatchList = new BatchList();
            $this->BatchList->recursive = -1;
            $batchData = $this->BatchList->findById($batch_id);

            $usersData = $userAttachedToUnit = array();
            $usersIncluded[0] = $userId;

            if ($batchData) {
                App::Import("Model", "ServiceUnit");
                $this->ServiceUnit = new ServiceUnit();
                $serviceUnit = $this->ServiceUnit->find('first', array(
                    'conditions' => array('ServiceUnit.unit_code' => $batchData['BatchList']['unit_code']),
                    'fields' => array('ServiceUnit.id')
                ));

                App::Import("Model", "UserServiceUnit");
                $this->UserServiceUnit = new UserServiceUnit();
                $userServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.service_unit_id' => $serviceUnit['ServiceUnit']['id']),
                                        'fields' => array('UserServiceUnit.user_id'))
                                    );

                if (!empty($userServiceUnit)) {
                    foreach ($userServiceUnit as $includedUserId) {
                        $usersIncluded[] = $includedUserId;
                        $userAttachedToUnit[] = $includedUserId;
                    }
                }
            }

            if (!empty($batchData)) {
                //foreach ($batchData as $fetcheddData){
                $usersIncluded[] = $batchData['BatchList']['prepared_by'];
                $usersIncluded[] = $batchData['BatchList']['verified_by'];
                $usersIncluded[] = $batchData['BatchList']['approved_by'];
                $usersIncluded[] = $batchData['BatchList']['endorse_by'];
                //}
                $usersIncluded = array_unique($usersIncluded);
            }
            $this->User->recursive = -1;
            $userDataFetched = $this->User->find('all', array(
                'conditions' => array('User.id' => $usersIncluded),
                'fields' => array('User.e_name', 'User.id', 'User.on_leave', 'User.email')
            ));

            $userOnLeave = $userEmail = array();
            foreach ($userDataFetched as $fetchedUserData) {
                $usersData[$fetchedUserData['User']['id']] = $fetchedUserData['User']['e_name'];
                $userEmail[$fetchedUserData['User']['id']] = $fetchedUserData['User']['email'];
                if($fetchedUserData['User']['on_leave'] == 1){
                    $userOnLeave[] = $fetchedUserData['User']['id'];
                }
            }
            
            $mail = array();
            $toUser = $ccUser = $allUser = $toUserEmails =  $ccUserEmails = $allUserEmail = array();
            if(!empty($userAttachedToUnit)){
                $mail = array('Mail' => array(
                            'mail_type' => 'Part time Payroll Status', 
                            'sent_by' => $userId,
                            'batch_status' => $batchData['BatchList']['approved_status'],
                            'batch_unit_code' => $batchData['BatchList']['unit_code'],
                            'batch_prepared_by' => $batchData['BatchList']['prepared_by'],
                            'batch_prepared_date' => $batchData['BatchList']['prepared_date'],
                            'batch_verified_by' => $batchData['BatchList']['verified_by'],
                            'batch_verified_date' => $batchData['BatchList']['verified_date'],
                            'batch_approved_by' => $batchData['BatchList']['approved_by'],
                            'bacth_approved_date' => $batchData['BatchList']['approved_date'],
                            'remark' => $remark)
                        );
                foreach($userAttachedToUnit as $userToUnit){
                    if(!in_array($userToUnit, $userOnLeave)){
                        $toUser[] = $usersData[$userToUnit];
                        $toUserEmails[] = $userEmail[$userToUnit];
                        $mail['UserMail'][] = array('user_id' => $userToUnit, 'mail_type' => 'to');
                    } else{
                        $ccUser[] = $usersData[$userToUnit];
                        $ccUserEmails[] = $userEmail[$userToUnit];
                        $mail['UserMail'][] = array('user_id' => $userToUnit, 'mail_type' => 'cc');
                    }
                    $allUser[] = $usersData[$userToUnit];
                    $allUserEmail[] = $userEmail[$userToUnit];
                }
            }

            $ccUsers = (!empty($ccUser)) ? implode(', ', $ccUser) : 'None';
            $toUsers = (!empty($toUser)) ? implode(', ', $toUser) : 'None';
            $batchStatus = ($batchData['BatchList']['approved_by'] == 0) ? 'Yet to Approved' : 'Approved';
            $batchPreparedBy = ($batchData['BatchList']['prepared_by']) ? $usersData[$batchData['BatchList']['prepared_by']] : '--' ;
            $batchVerifiedBy = ($batchData['BatchList']['verified_by']) ? $usersData[$batchData['BatchList']['verified_by']] : '--';
            $batchApprovedBy = ($batchData['BatchList']['approved_by']) ? $usersData[$batchData['BatchList']['approved_by']] : '--';
            $verifiedStatus = ($batchData['BatchList']['verified_date'] != '0000-00-00') ? date('M j, Y', strtotime($batchData['BatchList']['verified_date'])) :  'YET_TO_VERIFY';
            $approvedStatus = ($batchData['BatchList']['approved_date'] != '0000-00-00') ? date('M j, Y', strtotime($batchData['BatchList']['approved_date'])) : 'YET_TO_APPROVE';
            
            $msg = '<table cellpadding="5">
                        <tr><th style="text-align: right; width:10%;">From:</th><td>' .$usersData[$userId]. '</td></tr>
                        <tr>
                            <th style="text-align: right;">To :</th>
                            <td>'.$toUsers.'</td>
                        </tr>
                        <tr><th style="text-align: right;">Cc :</th><td>'. $ccUsers .'</td></tr>
                        <tr><th style="text-align: right;">Subject :</th><td>Part time payroll approval status</td></tr>
                        <tr><th style="text-align: right;">Content :</th><td>Status : '. $batchStatus .'</td></tr>
                        <tr><th style="text-align: right;">&nbsp;</th><td>Batch Date : '. date('M j, Y', strtotime($batchData['BatchList']['prepared_date'])). '</td></tr>
                        <tr><th style="text-align: right;">&nbsp;</th><td>Service Unit : '. $batchData['BatchList']['unit_code']. '</td></tr>
                        <tr>
                            <th style="text-align: right;">&nbsp;</th>
                            <td>
                                <table style="width:100%">
                                    <tr>
                                        <th style="width:33%; text-align: left;">Created</th>
                                        <th style="width:33%; text-align: left;">Verified</th>
                                        <th style="width:33%; text-align: left;">Approved</th>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr>
                            <th style="text-align: right;">By</th>
                            <td>
                                <table style="width:100%">
                                    <tr>
                                        <td style="width:33%">'. $batchPreparedBy .'</td>
                                        <td style="width:33%">'. $batchVerifiedBy .'</td>
                                        <td style="width:33%">'. $batchApprovedBy .'</td>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr>
                            <th style="text-align: right;">Date</th>
                            <td>
                                <table style="width:100%">
                                    <tr>
                                        <td style="width:33%">'. date('M j, Y', strtotime($batchData['BatchList']['prepared_date'])).'</td>
                                        <td style="width:33%">'. $verifiedStatus .'</td>
                                        <td style="width:33%">'. $approvedStatus .'</td>
                                    </tr>
                                </table>
                            </td>
                        </tr>

                        <tr>
                            <th>&nbsp;</th>
                            <th style="text-align: left;">Authorized Staff</th>
                        </tr>
                        <tr>
                            <th style="text-align: right;">&nbsp;</th>
                            <td>'. implode(', ', $allUser).'</td>
                        </tr>

                        <tr>
                            <th>&nbsp;</th>
                            <th style="text-align: left;">Staff on Leave</th>
                        </tr>
                        <tr>
                            <th style="text-align: right;">&nbsp;</th>
                            <td>'. implode(', ', $ccUser).'</td>
                        </tr>
                        <tr>
                            <th style="text-align: right;">Remark</th>
                            <td>'.$this->request->data['Payroll']['remark'].'</td>
                        </tr>
                    </table>';
            
            //--------- Mail Chain----------------
            $Email = new CakeEmail();
            $Email->config('gmail');
            if(empty($toUserEmails)){
                $toUserEmails[] = 'slsahani@gmail.com'; 
            }
            $Email->from(array($userEmail[$userId] => $usersData[$userId]))
                    ->to($toUserEmails)
                    ->cc($ccUserEmails)
                    ->emailFormat('both')
                    ->subject('Part time payroll approval status')
                    ->send($msg);
            
            App::Import("Model", "Mail");
            $this->Mail = new Mail();
            if(!empty($mail)){
                $this->Mail->saveAssociated($mail, array('deep' => true));
            }
            
            $msg = __("MAIL_STATUS_SUCCESS", true);
            $this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
            return $this->redirect(array('action' => 'admin_verification_view/'.$batch_id));
        }
    }
    
    /* __________________________________________________________
     * @Method      :email_history
     * @Description :to show email history to logged in user
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_email_history() {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", "Mail");
        $this->Mail = new Mail();
        
        App::Import("Model", "User");
        $this->User = new User();
        
        if ($this->request->data) {
            //pr($this->request->data);
            $filterDate = $filterDateMin = $filterDateMax = '';
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] != ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
            }
            
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] == ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-1-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-12-31';
            }
            
            $conditions = array(
                'Mail.created >= ' => $filterDateMin,
                'Mail.created <= ' => $filterDateMax
            );
            //echo 'Date Min'. $filterDateMin;
            //echo '<br>Date Maxs'. $filterDateMax;
        } else{
            $filterDateMin = date("Y-m-1");
            $filterDateMax = date("Y-m-t");
            $conditions = array(
                'Mail.created >= ' => $filterDateMin,
                'Mail.created <= ' => $filterDateMax
            );
        }

        $mailData = $this->paginate('Mail', array($conditions));
        $userInvolved = array();
        if(!empty($mailData)){
            foreach($mailData as $mails){
                $userInvolved[] = $mails['Mail']['sent_by'];
            }
            $userInvolved = array_unique($userInvolved);
        }
        $userData = $this->User->find('list', array(
                    'conditions' => array('User.id' => $userInvolved),
                    'fields' => array('User.id', 'User.e_name')
                ));
      
        $this->set('mailData', $mailData);
        $this->set('userData', $userData);
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('email_list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :check_email_history
     * @Description :to show email history to logged in user
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_check_email_history($id = NULL) {$this->layout = 'pop_up_window';
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        App::Import("Model", array("Mail", "UserMail"));
        $this->Mail = new Mail();
        $this->UserMail = new UserMail();
        $this->UserMail->unBindModel(array('belongsTo' => array('Mail')), false);
        $this->UserMail->bindModel(array(
            'belongsTo' => array(
                'User' => array( )
            )
        ), false);
        
        $this->Mail->recursive = 2;
        $mailData = $this->Mail->findById($id);
        
        App::Import("Model", "User");
        $this->User = new User();
        $currentUserDetails = $this->User->findById($mailData['Mail']['sent_by']);
        
        App::Import("Model", "Role");
        $this->Role = new Role();
        $userRoles = $this->Role->find('list', array('fields' => array('Role.id', 'Role.role_name')));
        App::Import("Model", "Message");
        $this->Message = new Message();
        $messageOptions = $this->Message->find('list');
        
        $this->set('messageOptions', $messageOptions);
        $this->set('mailData', $mailData);
        $this->set('userRoles', $userRoles);
        $this->set('currentUserDetails', $currentUserDetails);
    }
    
    /* __________________________________________________________
     * @Method      :admin_batch_verification
     * @Description :To show list of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_verification_index() {
        $userId = $this->Auth->User("id");
        $userType = $this->Auth->User('user_type');
        
        //$this->BatchList->recursive = -1;
        
        $savecrit = '';

        //criteria array for search
        $crit = array();
        $crit[] = "";
        
        $filterMonth = $filterYear = '';
        $conditions = array();
        if ($this->request->data) {
            //pr($this->request->data);
            $filterDate = $filterDateMin = $filterDateMax = '';
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] != ''){
                $filterDate = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'];
                $maxDate = date("t", strtotime($filterDate));
                $filterDateMin = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-'.$this->request->data['Payroll']['month'].'-'.$maxDate;
                
                $filterMonth = $this->request->data['Payroll']['month'];
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if($this->request->data['Payroll']['year'] != '' && $this->request->data['Payroll']['month'] == ''){
                $filterDateMin = $this->request->data['Payroll']['year'].'-1-1';
                $filterDateMax = $this->request->data['Payroll']['year'].'-12-31';
                
                $filterMonth = 1;
                $filterYear = $this->request->data['Payroll']['year'];
            }
            
            if ($this->request->data['Payroll']['search_criteria'] != '' && $filterDateMin == '' && $filterDateMax == '') {
                $searchCriteriaTerm = trim($this->data['Payroll']['search_criteria']);
                //will search with name and abbreviation
                //$crit[] = "(BatchList.unit_code like '%" . $searchCriteriaTerm . "%')";
                $conditions = array('BatchList.unit_code like' => "%" . $searchCriteriaTerm . "%");

                //$savecrit .= "search_criteria:" . $searchCriteriaTerm . "/";
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->data['Payroll']['search_criteria'] == '') {
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax
                );
            } elseif ($filterDateMin != '' && $filterDateMax != '' && $this->request->data['Payroll']['search_criteria'] != '') {
                $searchCriteriaTerm = trim($this->request->data['Payroll']['search_criteria']);
                $conditions = array(
                    'BatchList.prepared_date >= ' => $filterDateMin,
                    'BatchList.prepared_date <= ' => $filterDateMax,
                    'BatchList.unit_code LIKE ' => "%" . $searchCriteriaTerm . "%",
                );
            }
            //echo 'Date Min'. $filterDateMin;
            //echo '<br>Date Maxs'. $filterDateMax;
        } else{
            $filterDateMin = date("Y-m-1");
            $filterDateMax = date("Y-m-t");
            $conditions = array(
                'BatchList.prepared_date >= ' => $filterDateMin,
                'BatchList.prepared_date <= ' => $filterDateMax
            );
            
            $filterMonth = date("m");
            $filterYear = date("Y");
        }
        
        App::Import("Model", "ServiceUnit");
        $this->ServiceUnit = new ServiceUnit();
        
        $serviceUnitCondition = array();
        //=============Service Unit Associated to logged in user==================
        if($userId !=1 ){
            App::Import("Model", "UserServiceUnit");
            $this->UserServiceUnit = new UserServiceUnit();
            $associatedServiceUnit = $this->UserServiceUnit->find('list', array(
                                        'conditions' => array('UserServiceUnit.user_id' => $userId), 
                                        'fields' => array('UserServiceUnit.service_unit_id'))
                                    );
            $serviceCode = $this->ServiceUnit->find('list', array('conditions' => array('ServiceUnit.id' => $associatedServiceUnit),'fields' => array('ServiceUnit.unit_code')));
            //pr($serviceCode);
            $conditions['BatchList.unit_code'] = $serviceCode;
            $serviceUnitCondition['ServiceUnit.unit_code'] = $serviceCode;
        }
        //=============Service Unit Name for Associated Unit==================
        $serviceUnit = $this->ServiceUnit->find('list', array('conditions' => $serviceUnitCondition, 'fields' => array('ServiceUnit.unit_code', 'ServiceUnit.unit_name')));
        
        //$this->BatchList->recursive = -1;
        $this->BatchList->unBindModel(array('hasMany' => array('Payable')) ,false);
        $this->BatchList->bindModel(array(
            'belongsTo' => array(
                'ServiceUnit' => array(
                    'foreignKey' => false,
                    'conditions' => 'ServiceUnit.unit_code = BatchList.unit_code'
                )
            )
        ), false);
        $this->Paginator->settings = array('order' => array('BatchList.id' => 'desc'));
        $batchData = $this->paginate('BatchList', array($conditions));
        //pr($batchData);
        
        $batch_list_id_array = array();
        if(!empty($batchData)){
            foreach($batchData as $batch){
                $batch_list_id_array[] = $batch['BatchList']['id'];
            }
        }
        
        //---- On hold count---
        App::Import("Model", "Payable");
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('on_hold_count' => 'COUNT(Payable.on_hold)');
        $payableOnHoldCount = $this->Payable->find('list', 
                            array('fields' => array('Payable.batch_list_id', 'Payable.on_hold_count'), 
                                'group' => array('Payable.batch_list_id'),
                                'conditions' => array('Payable.batch_list_id' => $batch_list_id_array, 'Payable.on_hold' => 1)
                            )
                        );
        
        $this->Payable = new Payable();
        $this->Payable->virtualFields = array('total_payable' => 'SUM(Payable.amount_payable)', 'payable_count' => 'COUNT(Payable.id)');
        $payableTotalCount = $this->Payable->find('all', array(
                                        'conditions' => array('Payable.on_hold !=' => 1, 'Payable.batch_list_id' => $batch_list_id_array),
                                        'fields' => array('Payable.batch_list_id', 'Payable.total_payable', 'Payable.payable_count'), 
                                        'group' => array('Payable.batch_list_id'),
                                    )
                                );
        
        $payableCount = $payableTotal = array();
        if(!empty($payableTotalCount)){
            foreach($payableTotalCount as $payableCounts){
                $payableCount[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__payable_count'];
                $payableTotal[$payableCounts['Payable']['batch_list_id']] = $payableCounts['0']['Payable__total_payable'];
            }
        }
        
        
        $usersData = array();
        if(!empty($batchData)){
            $usersIncluded = array();
            foreach ($batchData as $fetcheddData){
                $usersIncluded[] =  $fetcheddData['BatchList']['prepared_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['verified_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['approved_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['endorse_by'];
                $usersIncluded[] =  $fetcheddData['BatchList']['checker'];
                $usersIncluded[] =  $fetcheddData['BatchList']['admin_verifier'];
            }
            $usersIncluded = array_unique($usersIncluded);
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }

        /*$batchDetails = $batchData;
        $newBatachData = $tempArray = array();
        if(!empty($batchDetails)){
            foreach($batchDetails as $key => $details){
                foreach($details['BatchList'] as $subKey => $value){
                    $tempArray['BatchList'][$subKey] = $value;
                    if($subKey == 'unit_code'){
                        $tempArray['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
                    }
                }
                $newBatachData[$key] = $tempArray; 
                $newBatachData[$key]['BatchList']['payable_count'] = count($details['Payable']); 
                //$batchData[$key]['BatchList']['unit_name'] = isset($serviceUnit[$details['BatchList']['unit_code']]) ? $serviceUnit[$details['BatchList']['unit_code']] : '';
            }
        }*/
        
        $this->set('filterMonth', $filterMonth);
        $this->set('filterYear', $filterYear);
        
        $this->set('payableOnHoldCount', $payableOnHoldCount);
        $this->set('payableCount', $payableCount);
        $this->set('payableTotal', $payableTotal);
        $this->set('serviceUnit', $serviceUnit);
        $this->set('batchData', $batchData);
        $this->set('usersData', $usersData);
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_verification_list', 'ajax');
        }//if
    }
    
    /* __________________________________________________________
     * @Method      :admin_verification_view
     * @Description :To view details of Payroll Batch
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function admin_verification_view($id  = null) {
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        
        if (!$id) {
            throw new NotFoundException(__('INVALID_PAYROLL'));
        }
        $this->BatchList->recursive = -1;
        $batchListData = $this->BatchList->findById($id);
        
        $this->Payable->recursive = -1;
        $this->Payable->virtualFields = array('amount' => 'ROUND(Payable.amount_payable)');
        $condition = array('Payable.batch_list_id'  => $id);
        $payableList = $this->paginate('Payable', array($condition));
        
        $usersData = array();
        if(!empty($batchListData)){
            $usersIncluded = array();
            $usersIncluded[] =  $batchListData['BatchList']['prepared_by'];
            $usersIncluded[] =  $batchListData['BatchList']['verified_by'];
            $usersIncluded[] =  $batchListData['BatchList']['approved_by'];
            $usersIncluded[] =  $batchListData['BatchList']['endorse_by'];
            $usersIncluded[] =  $batchListData['BatchList']['checker'];
            $usersIncluded[] =  $batchListData['BatchList']['admin_verifier'];
            $usersIncluded = array_unique($usersIncluded);
            
            $fileDatas = $this->User->find('all', array(
                        'conditions' => array('User.id' => $usersIncluded),
                        'fields' => array('User.e_name', 'User.id')
                    ));
            foreach($fileDatas as $fetcheUserData){
                $usersData[$fetcheUserData['User']['id']] = $fetcheUserData['User']['e_name'];
            }
        }
        
        $this->set('payableList', $payableList);
        $this->set('usersData', $usersData);
        
        if (!$batchListData) {
            throw new NotFoundException(__('INVALID_BATCH'));
        } else{
            $this->set('batchListData', $batchListData);
        }
        
        if ($this->request->is('ajax')) {
            $this->viewPath = 'elements' . DS . 'payrolls';
            $this->render('admin_verification_payable_list', 'ajax');
        }
        
    }
    
    
    /* __________________________________________________________
     * @Method      :adminVerifierChangeStatus
     * @Description :to change status of a Batch List/Approve/Revert
     * @access      :null
     * @param      :null
     * @return     :null
     */
    public function adminVerifierChangeStatus() {
        $this->autoRender = false;
        $this->layout = '';
        
        $userId   = $this->Auth->User("id");
	$userType = $this->Auth->User('user_type');
        
        $id = $this->request->data['Payroll']['batch_list_id'];
        App::Import("Model", "BatchList");
        $this->BatchList = new BatchList();
        $this->BatchList->recursive =-1;
        $batchDetails = $this->BatchList->find('first', array('conditions' => array('BatchList.id' => $id)));
        
        if(isset($this->request->data['checking'])){
            $batchData['BatchList'] = array(
                            'id' => $id,
                            'checker' => $userId,
                            'check_date' => date('Y-m-d'),
                        );
            
        }
        if(isset($this->request->data['revert_checking'])){
            $batchData['BatchList'] = array(
                            'id' => $id,
                            'checker' => 0,
                            'check_date' => '0000-00-00',
                        );
            
        }
        
        if(isset($this->request->data['checked'])){
            $batchData['BatchList'] = array(
                                'id' => $id,
                                'admin_checked' => $userId,
                                'admin_checked_date' => date('Y-m-d'),
                            );
        }
        
        if(isset($this->request->data['revert_checked'])){
            $batchData['BatchList'] = array(
                                'id' => $id,
                                'admin_checked' => 0,
                                'admin_checked_date' => '0000-00-00',
                            );
        }
        
        $this->BatchList->save($batchData);
        $msg = __("UPDATE_STATUS_SUCCESS", true);
	$this->Session->setFlash('<div class="totalSuccess">' . $msg . '</div>');
        return $this->redirect(array('action' => 'admin_verification_view/'.$id));
    }
}